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

Question

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.

Thanks!

Serious

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? – Serious

Answer 1

You need Set with objects:

 Set rng = Sheet8.Range("A12")

Sheet8 is fine.

 Sheet1.[a1]

Backlash

+1 thanks doesn't gimme an error but doesn't set the A12 Cell value either? Should it be rng.Value or rng.Formula doesn't work either? – Serious

lol! ok now Set rng.Value2 = "1" Gives me a runtime error 424 – Serious

@giddy I am back :) You only use Set to set a variable to an object. The code you posted works fine for me with the addition of Set, as per my answer. Does Sheet8 exist? Do not forget I can rename Sheet3 as Sheet8 but it is still Sheet3. Do not confuse the object Sheet8 with the sheet named Sheet8. – Backlash

BTW You do not need quotes for numbers, but that has nothing to do with the problem. – Backlash

@Remou aha! So it works at my home machine! And i see I can add an excel form control and an ActiveX Button but it works with both! I guess I probably did something silly at the work machine! Thanks so much! =D – Serious

Hé, happens to me all the time :) – Backlash

Answer 2

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.

Verein

Answer 3

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

Verein

Answer 4

Please check the Registry Editor or Component Services.

Registry:

Component:

Quarto

What should I look for in either the registry or the components? Please edit your answer to explain what to do with those. See How to Answer. – Pedropedrotti