Variable not set Error!

L

luolovepi

Board Regular
Joined
Jun 9, 2011
Messages
116
I just write this test code. Sometimes it is correct the first time I run it. Sometimes it can select the cell I specified, but will shows the error: Object Required! But it always results in error when I try to run it the second time and so on and so forth. VBE always say "variable not set or something". I actually already initiate Pr when the workbook is open.
Does anyone meet this kind of error before and know how to solve this problem?
Thank you.
Code: 
Sub test()
Pr.Range("N59").Activate.Select
End Sub

Yours sincerely,
lolo^-^
 
Last edited:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
your VBA project can get reset due to various reasons, with the effect that all initialised variables lose their contents.
You should test if it is initialised before using it...
 
Upvote 0
Just to add to Hermanito's comments: if you have 'Pr' referred to anywhere than ThisWorkbook, it would need to be declared Public, which is probably unnecessary. Why not just declare it in the procedures (Sub or Function) where it is needed, and Set it each time, or, pass the reference as an argument.
 
Upvote 0
If Pr is a sheet, the best choice is to assign this name to sheet in Project Explorer (so called programmatic name).

Do the following:
1. In Project Explorer select the sheet you want to be "Pr".
2. Press F4 or choose View -> Properties Window.
3. At the top of appeared window you will see (Name) property - with parenthesis. Type into corresponding text box Pr.

After you do this, you can just use Pr - this will ALWAYS be your sheet (you can rename sheet's tab - this won't affect (Name) property).
 
Upvote 0
Thank you Mark~
Actually I indeed need to use Pr elsewhere. And Pr is initiated when this workbook is open.
If declaring it every time I will use it, I feel it is really too messy.

lolo

GTO said:
Just to add to Hermanito's comments: if you have 'Pr' referred to anywhere than ThisWorkbook, it would need to be declared Public, which is probably unnecessary. Why not just declare it in the procedures (Sub or Function) where it is needed, and Set it each time, or, pass the reference as an argument.
Click to expand...
 
Upvote 0
Hi Sektor,
Thanks for the help!
What do you mean by Project Explorer?
And for step "2". When I choose View in the Ribbon, I can't find "Properties Window". Maybe I see the wrong place?

lolo
Sektor said:
If Pr is a sheet, the best choice is to assign this name to sheet in Project Explorer (so called programmatic name).

Do the following:
1. In Project Explorer select the sheet you want to be "Pr".
2. Press F4 or choose View -> Properties Window.
3. At the top of appeared window you will see (Name) property - with parenthesis. Type into corresponding text box Pr.

After you do this, you can just use Pr - this will ALWAYS be your sheet (you can rename sheet's tab - this won't affect (Name) property).
Click to expand...
 
Upvote 0
Ah! You must be in VBE! Press Alt+F11. On the left side (usually), there's a docked window called "Project - VBAProject". There're all your worksheets, modules etc. Press "+" sign next to "Microsoft Excel Object" to discover all Excel objects. There are all your sheets. Then follow my previous post.
 
Upvote 0
Oh thank you~~~~
Sektor said:
Ah! You must be in VBE! Press Alt+F11. On the left side (usually), there's a docked window called "Project - VBAProject". There're all your worksheets, modules etc. Press "+" sign next to "Microsoft Excel Object" to discover all Excel objects. There are all your sheets. Then follow my previous post.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Error passing variables to a userform
Replies
6
Views
72
Ark68
A
M
  • Question Question
Issue with UDF's showing #NAME? error
Replies
7
Views
279
MARK858
MARK858
A
  • Solved
Procedure fails on some sheets only
Replies
2
Views
60
AlexanderBB
A
O
  • Question Question
Cannot trap error when using Excel's built-in stock price functionality in a macro. It used to work well!
Replies
1
Views
249
OaklandJim
O
zookeepertx
  • Question Question
Find location of a string to use in a For Loop
Replies
3
Views
83
Joe4
Joe4

Forum statistics

Threads
1,224,518
Messages
6,179,258
Members
452,901
Latest member
LisaGo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top