Object Variable or With Block variable not set using Excel-VBA

Question

I have two subs called FindTempRow and FindBatchRow

Sub FindTempRow()  
    With Worksheets("TEMPLATES").Range("G:G")

        Set t = .Find("Template", LookIn:=xlValues)
        If Not t Is Nothing Then
            FirstAddress1 = t.Address
            Do
                Call FindBatchRow
                Set t = .FindNext(t)
            Loop While Not t Is Nothing And t.Address <> FirstAddress1
        End If
    End With
End Sub


Sub FindBatchRow()
        With Worksheets("DISTRIBUTION LIST").Range("C:C")
            Set d = .Find(BatchNo, LookIn:=xlValues)
                If Not d Is Nothing Then
                    FirstAddress2 = d.Address
                    Do
                        Set d = .FindNext(d)
                    Loop While Not d Is Nothing And d.Address <> FirstAddress2
                End If
        End With
End Sub

FindTempRow is calling FindBatchRow inside a Do While Loop.

The problem is whenever I run the code it gives me an error: Runtime Error Code (91) Object Variable or With Block variable not set

The code that gives an error is located in FindTempRow:

Loop While Not t Is Nothing And t.Address <> FirstAddress1

I tried removing call FindBatchRow in Sub FindTempRow and it runs fine. It seems my code is forgetting the address value of t whenever another find method is called in sub FindBatchRow

SOLUTION: by @Rory

REPLACE: Set t = .FindNext(t) from Sub FindBatchRow

WITH: Set t = .Find("Template", After:=t, LookIn:=xlValues)

Please step throught the code with F8. At which line does the error occur then? — Julian Kuchlbauer, Aug 16 '16 at 07:03
`Loop While Not t Is Nothing And t.Address <> FirstAddress1` — Chad Patrick, Aug 16 '16 at 07:06
@ChadPatrick are these 2 `Sub`s code in the same module ? where and how is `t` defined ? — Shai Rado, Aug 16 '16 at 07:09
Possible duplicate of [Excel VBA Boolean Expression - Order of Operations](http://stackoverflow.com/q/345399/11683) and [What is a NullReferenceException, and how do I fix it?](http://stackoverflow.com/q/4660142/11683). — GSerg, Aug 16 '16 at 07:11
the 2 subs are inside the same form not module, and t is defined inside the code which is `set t = .Find("Template", LookIn:=xlValues)` — Chad Patrick, Aug 16 '16 at 07:15
`FindNext` repeats the **last** search. Since you changed the search parameters in your `FindBatchRow` routine, you are actually repeating that search,not the original one. You need to use `Find` again and specify the same parameters. — Rory, Aug 16 '16 at 08:32
Instead of `FindNext` repeat the `Find`: `Set t = .Find("Template", After:=t, LookIn:=xlValues)` — Rory, Aug 16 '16 at 09:10

score 2 · Answer 1 · answered Aug 16 '16 at 07:10

In the first run of the loop, if nothing is found then, when your code attempts to get address from t which is actually nothing, it throws error. Remove this part from your loopAnd t.Address <> FirstAddress1 . Instead check this condition inside the do loop with a If statement and when true jump out of the loop usingExitDo

score 0 · Answer 2 · answered Aug 16 '16 at 07:13

VBA always resolves all parts of in the if clause (or here in the while clause) so if in an you use the and operator and the first one is false, the second one gets checked nevertheless, which fails if t is nothing. Do something like this:

Do
    Call FindBatchRow
    Set t = .FindNext(t)
    Dim a As boolean
    a = False
    If Not t is Nothing
        If t.Address <> FirstAddress1
            a = True
        End If
    End If
Loop While a

The same should be done in your FindBatchRow accordingly.

Object Variable or With Block variable not set using Excel-VBA

2 Answers2