CODEWITHSUNDEEP
excelvba
Keith Marwood
Keith Marwood

Reputation: 23

Range.Find produces error when find fails

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

Upvotes: 2

Views: 73

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

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

Upvotes: 4

Related Questions