checking and setting a value to a range in VBA

[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

 

[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).

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

 

[Jim Thomlinson]

Try
iRow = range("MyRange").row

 

[LetMeDoIt]

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

Jim Thomlinson









- Show quoted text -

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

 

[Rick Rothstein]

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)

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

Jim Thomlinson









- Show quoted text -

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

 

[LetMeDoIt]

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 -

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

 

[LetMeDoIt]

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 -

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

CG