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
– Julian Kuchlbauer, Commented Aug 16, 2016 at 7:03
Loop While Not t Is Nothing And t.Address <> FirstAddress1 — Chad Patrick
– Chad Patrick, Commented Aug 16, 2016 at 7:06
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
– Rory, Commented Aug 16, 2016 at 8:32
Instead of FindNext repeat the Find: Set t = .Find("Template", After:=t, LookIn:=xlValues) — Rory
– Rory, Commented Aug 16, 2016 at 9:10

cyboashu · Accepted Answer · 2016-08-16 07:10:11Z

2

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

answered Aug 16, 2016 at 7:10

cyboashu

10.5k2 gold badges32 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Julian Kuchlbauer · Accepted Answer · 2016-08-16 07:13:25Z

0

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.

answered Aug 16, 2016 at 7:13

Julian Kuchlbauer

8951 gold badge8 silver badges17 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related