22

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!

Community
  • 1
  • 1
gideon
  • 19,329
  • 11
  • 72
  • 113
  • 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 Mar 12 '11 at 09:49

3 Answers3

37

You need Set with objects:

 Set rng = Sheet8.Range("A12")

Sheet8 is fine.

 Sheet1.[a1]
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • +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? – gideon Mar 12 '11 at 09:58
  • lol! ok now `Set rng.Value2 = "1"` Gives me a runtime error 424 – gideon Mar 12 '11 at 10:00
  • @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. – Fionnuala Mar 12 '11 at 14:18
  • BTW You do not need quotes for numbers, but that has nothing to do with the problem. – Fionnuala Mar 12 '11 at 14:19
  • @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 – gideon Mar 12 '11 at 14:27
  • Hé, happens to me _all the time_ :) – Fionnuala Mar 12 '11 at 14:30
0

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.

Swapnil P
  • 1
0

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
General Grievance
  • 4,555
  • 31
  • 31
  • 45
Swapnil P
  • 1