Range.Find produces error when find fails

user_17007642 0
Viewed 47

These are two simple lines of code to find an instance of a 6 digit number in a list. If the number exists, everything works perfectly but if not, I get the error "Object variable or With block variable not set"

The lines of code are

    COL = Cells(5, "A").Text
    RowFound = Sheets("Master List").Columns("A").Find(What:=COL).Row

The cell format is Text in the column and in the search data cell

Any advice on this would be helpful and much appreciated

vba excel
1 Answers

This is the way VBA works. VBA cannot return a row from a not existing range.

Please, try the next way:

Sub testFindFail()
  Dim c As Range, RowFound As Long, COL As String
  COL = cells(5, "A").text
  Set c = Sheets("Master List").Columns("A").Find(What:=COL) 'it will be good to also use some other parameters.
  If c Is Nothing Then Exit Sub 'or do something else
  RowFound = c.row
  Debug.Print RowFound
End Sub
user_22333080
Related