When trying to read #NAME? Value from excel to a string gives Type mismatch error

Question

I have a macro which reads a description string from excel and finds out the particular string is present in the data or not.

Sometimes the input file might contain a value like #NAME? in the cell. When the macro reaches this cell its just gives error type mismatch Run time error 13 on the following line.

I just wanted to ignore this line and continue the with the next line. How I should I give a validation (if). I'm using a 'do- loop until' for the loop. descriptionString is a string variable.

descriptionString = currentwrkbk.Worksheets(1).Cells(i, 1).Value

Answer 1

Use IsError, something like so

if iserror(currentwrkbk.Worksheets(1).Cells(i, 1).Value))....

Answer 2

you may want to iterate through valid cells only using SpecialCells method of Range object, that allows you to filter a range returning a range with "all the cells that match the specified type and value"

for instance

myRange.SpecialCells(XlCellType.xlCellTypeConstants, xlTextValues)

will return myRange cells containing constant (i.e. not resulting from formulas) text values only

so that a possible snippet for your purposes could be the following:

Option Explicit

Sub main()
    Dim cell As Range
    Dim currentwrkbk As Workbook
    Dim descriptionString As String

    With currentwrkbk.Worksheets(1) 
        For Each cell In .Range("A1", .Cells(.Rows.Count, 1).End(xlUp)).SpecialCells(XlCellType.xlCellTypeConstants, xlTextValues) '<--| loop through text values of column "A" only

            descriptionString = cell.value

        Next cell
    End With
End Sub