CODEWITHSUNDEEP
excelvba
Samadhi Ghosh
Samadhi Ghosh

Reputation: 31

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

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.

Upvotes: 0

Views: 337

Answers (1)

Tim Williams
Tim Williams

Reputation: 166980

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)

Upvotes: 1

Related Questions