simple vba code gives me run time error 91 object variable or with block not set

22
votes

So I have a simple little macro/sub defined when a command button is clicked. The problem is it gives me:

Run Time Error '91' : Object Variable or With Block not Set

My code is:

Dim rng As Range
rng = Sheet8.Range("A12") '<< ERROR here
rng.Value2 = "1"

I just want to set Cell "A12" in Sheet8.

enter image description here

Thanks!

3
vbaexcelexcel-2007
I'm sure there is a Sheet8 and there is certainly a cell A12 the sheet. Maybe its the syntax? Should it be "A" and "12" separately? – gideon

3 Answers

37
votes

You need Set with objects:

 Set rng = Sheet8.Range("A12")

Sheet8 is fine.

 Sheet1.[a1]
0
votes

Check the version of the excel, if you are using older version then Value2 is not available for you and thus it is showing an error, while it will work with 2007+ version. Or the other way, the object is not getting created and thus the Value2 property is not available for the object.

0
votes

Also you are trying to set value2 using Set keyword, which is not required. You can directly use rng.value2 = 1

below test code for ref.

Sub test()
    Dim rng As Range
    Set rng = Range("A1")
    rng.Value2 = 1
End Sub