How to Stop Error '91':object variable or with block variable not set

Question

Hi I'm new to VBA so apologies if this is a really simple fix.

The following code keeps returning Excel Error '91': object variable or with block variable not set.

Private Sub Worksheet_Change(ByVal Target As Range)

 If Intersect(Target, Range("$B$7")) = Worksheets("Team Amendment Tables").Range("$C$7") Then
 Application.Run "TargetUpdate1"

End If

End Sub

Any thoughts on how to resolve this? Context: Drop Down List in Sheet 7 to trigger Macro: TargetUpdate1 Once without loop.

YowE3K · Accepted Answer · 2017-09-15 01:15:56Z

2

Your Intersect will return Nothing if any cell other than B7 is changed, so test for that first:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("$B$7")) Is Nothing Then
        If Range("$B$7").Value = Worksheets("Sheet1").Range("$C$7").Value Then
            Application.Run "TargetUpdate1"
        End If
    End If

End Sub

answered Sep 15, 2017 at 1:15

YowE3K

24k7 gold badges28 silver badges45 bronze badges

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

1 Comment

Luke Over a year ago

Absolute Legend. Thanks for your help!

Collectives™ on Stack Overflow

How to Stop Error '91':object variable or with block variable not set

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related