checking and setting a value to a range in VBA

  • Thread starter Thread starter LetMeDoIt
  • Start date Start date
L

LetMeDoIt

Greetings,
I'm trying to do an easy thing (I think) but have not succeeded thus
far.

I created a name range manually in a spreadsheet, but need to check
via VBA code for the value of that range, then if it changed to its
default, reset that range to a specific value.

To check the that the row has not changed, I do the following: iRow =
Range("=MyRange").Row
and thus if this is not my original value, I reassigned it ( this is
where I'm getting an error msg in the code).

I'm using excel 2003.

Many thanks.
CG
 
Greetings,
I'm trying to do an easy thing (I think) but have not succeeded thus
far.

I created a name range manually in a spreadsheet, but need to check
via VBA code for the value of that range, then if it changed to its
default, reset that range to a specific value.

To check the that the row has not changed, I do the following: iRow =
Range("=MyRange").Row
and thus if this is not my original value, I reassigned it ( this is
where I'm getting an error msg in the code).

Or alternatively, I'd like to dynamically set that range to soemthing specific, like: "sheet1!$B$10:$B$30"

I'm using excel 2003.

Many thanks.
CG
Click to expand...
 
hhhmmm, but I need to set the range, like something like
"sheet1!B10:B30"
Click to expand...

Try it this way...

Names("MyRange").RefersTo = "=Sheet1!$B$10:$B$30"

--
Rick (MVP - Excel)


Try
iRow = range("MyRange").row
--
HTH...

Jim Thomlinson









- Show quoted text -
Click to expand...

hhhmmm, but I need to set the range, like something like "sheet1!
B10:B30"
 
Try it this way...

Names("MyRange").RefersTo = "=Sheet1!$B$10:$B$30"

--
Rick (MVP - Excel)







hhhmmm, but I need to set the range, like something like "sheet1!
B10:B30"- Hide quoted text -

- Show quoted text -
Click to expand...

I'm getting "application-defined or object-defined error". I'm using
office 2003. Can that be an issue?
 
Try it this way...

Names("MyRange").RefersTo = "=Sheet1!$B$10:$B$30"

--
Rick (MVP - Excel)







hhhmmm, but I need to set the range, like something like "sheet1!
B10:B30"- Hide quoted text -

- Show quoted text -
Click to expand...

never mind... i added application. in front of your statement, and
it's working like a charm. Many thanks for your help.

CG
 
Back
Top