Find generates Run time Error 91 object variable or with block variable not set

Question

I am trying to highlight a row based on a number present in a specific cell of that row.

Sub searchTest()
    Dim firststepaddress As String
    Dim i As Long

    For i = 1 To 12
        Selection.Find(What:=i, After:=ActiveCell, LookIn:=xlFormulas, _
          LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
          MatchCase:=True).Activate 'error at this line
        firststepaddress = ActiveCell.Address
        Call cellRangeHighlighter
        MsgBox i
    Next i

I get

runtime error 91

After clicking debug, the below code is highlighted:

For i = 2 To 12
    Selection.Find(What:=i, After:=ActiveCell, LookIn:=xlFormulas, _
                   LookAt:=xlWhole, SearchOrder:=xlByRows, _
                   SearchDirection:=xlNext, MatchCase:=True).Activate

What I tried:

Attempt 1:
I removed the For loop and entered the number manually like 1 (What:=1) or 2 (What:=2) and so on and the code works.

Attempt 2:
Selection.Find(What:="" & i, After.....) 'still the same error message

Expected Output:
The Selection.Find function should be able to understand the number present in i and then proceed to call cellrangehighlighter sub-procedure.

Actual Output:
It highlights the row containing cell with number "1" and then throws the error.
If I change the range in the For loop from 2 to 12, then it highlights row having cell containing 2 and then throws the error.

You are not consistent in your description of the error. If you get error 91 on the line you are saying you are (`Selection.Find().Activate`), then it's a Possible duplicate of [Cells.Find() Raises “Runtime Error 91: Object Variable or With Block Not Set”](https://stackoverflow.com/q/29102052/11683). However in that case it would not "highlight the cell containing 2" because it would have not found it. Unless the error is on the next iteration, so it finds 2, but not 3, at which point it is a duplicate again. — GSerg, Jun 09 '19 at 19:37
Make sure that selection contains whatever is passed to `What:=`, otherwise you will get the error that you are getting right now (trying to activate range that doesn't exist, because nothing was found). — Ryszard Jędraszyk, Jun 09 '19 at 19:45
Help is here https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/object-variable-not-set-error-91. — Noodles, Jun 09 '19 at 22:12
Hi GSerg and Ryszard, there are no duplicate values, I assure you that. The cell does contain the numbers I am looking for. — Samadhi Ghosh, Jun 10 '19 at 01:27
@Noodles - the link is very informative. Thanks! I will keep in mind of the rules next time. — Samadhi Ghosh, Jun 10 '19 at 01:30
@Gser - I realized the stupid mistake I was doing.There was a piece of code above the subsearch procedure that highlights the column (Say column B) in which various cells (lets assume B2, B5, B8 etc) had values. When the cellrangehighlighter procedure was called, it activated the row in which it found cell containing number 1. On the next iteration, it was trying to find number 2 in the previously activated row which didnt had any. You were so right! Thanks — Samadhi Ghosh, Jun 10 '19 at 01:48

score 1 · Accepted Answer · answered Jun 09 '19 at 22:10

Here's the typical approach to using Find:

Dim sel as Range, f As Range
Set sel = Selection

For i = 2 To 12
    Set f = sel.Find(What:=i, After:=ActiveCell, LookIn:=xlFormulas, _
                   LookAt:=xlWhole, SearchOrder:=xlByRows, _
                   SearchDirection:=xlNext, MatchCase:=True)
    If Not f Is Nothing Then
         'do something with f
    Else
         'handle not found if needed
    End if

Always set the result to a variable, then check it's not Nothing before proceeding. And avoid Active/Select as much as possible (ie. almost always)

Hi @Tim, the solution actually removed the error. Thanks a ton for your help! — Samadhi Ghosh, Jun 10 '19 at 01:36

Find generates Run time Error 91 object variable or with block variable not set

1 Answers1