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.

enter image description here

Thanks!

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
– gideon, Commented Mar 12, 2011 at 9:49

Fionnuala · Accepted Answer · 2011-03-12 09:53:21Z

37

You need Set with objects:

 Set rng = Sheet8.Range("A12")

Sheet8 is fine.

 Sheet1.[a1]

answered Mar 12, 2011 at 9:53

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

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

6 Comments

gideon Over a year ago

+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 Over a year ago

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

Fionnuala Over a year ago

@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 Over a year ago

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

gideon Over a year ago

@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

|

Swapnil P · Accepted Answer · 2021-01-05 14:45:36Z

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.

answered Jan 5, 2021 at 14:45

Swapnil P

1

Comments

General Grievance · Accepted Answer · 2021-01-05 15:14:16Z

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

edited Jan 5, 2021 at 15:14

General Grievance

5,12039 gold badges40 silver badges60 bronze badges

answered Jan 5, 2021 at 14:49

Swapnil P

1

Comments

Adriaan · Accepted Answer · 2023-11-24 06:34:23Z

0

Please check the Registry Editor or Component Services.

Registry:

Component:

edited Nov 24, 2023 at 6:34

Adriaan

18.2k7 gold badges47 silver badges88 bronze badges

answered Nov 24, 2023 at 6:32

Pathanapong S.

53 bronze badges

1 Comment

Adriaan Over a year ago

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.

Collectives™ on Stack Overflow

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

4 Answers 4

6 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related