Saving Range Locatoin into a Variable
 

Hi Dale,

Since you declare oSave_Del_Rng as a Range object (and set it using Set ...
= ...), the object variable contains a reference to the Range object you set
it to [in this case, oSave_Del_Rng is the same as typing Range("Q215")].
Since the default property of a Range object is Value, you will get the
value of the range Q215 when you refer to your variable. To get the
address, you can use the Address property:

Debug.Print oSave_Del_Rng.Address

Regards,

Jake Marx
MS MVP - Excel


"Dale Cox" wrote in message
...
The following code executes just fine. The value moved
into oSave_Del_Rng, however, is the value in the Cell
Q215. I need the cell address, not the value of the cell,
moved into the variable.
Const sDelivery_Rng = "Q215"
Dim oSave_Del_Rng as Range
Set oSave_Del_Rng = Range(sDelivery_Rng)

Help!

Saving Range Locatoin into a Variable
 

Hi Bob,

"Bob Phillips" wrote in message
...
Jake,

You're back! Where have you been? I guess you must have a life<vbg.

Yeah, life has been taking up a lot of my time lately. <g A new baby and
job are the biggest time consumers right now. But they're definitely worth
it...at least the baby is. Hopefully, I'll be able to start spending some
more time here in the upcoming months.

Thanks,

Jake Marx
MS MVP - Excel

Saving Range Locatoin into a Variable
 

Sorry, but I'm relatively new to VBA. I tried the
following
Set oSave_Del_Rng.Address = Range(sDelivery_Rng)
which would not fly.

-----Original Message-----
Hi Dale,

Since you declare oSave_Del_Rng as a Range object (and
set it using Set ...
= ...), the object variable contains a reference to the
Range object you set
it to [in this case, oSave_Del_Rng is the same as typing
Range("Q215")].
Since the default property of a Range object is Value,
you will get the
value of the range Q215 when you refer to your
variable. To get the
address, you can use the Address property:

Debug.Print oSave_Del_Rng.Address

Regards,

Jake Marx
MS MVP - Excel


"Dale Cox" wrote in message
...
The following code executes just fine. The value moved
into oSave_Del_Rng, however, is the value in the Cell
Q215. I need the cell address, not the value of the
cell,
moved into the variable.
Const sDelivery_Rng = "Q215"
Dim oSave_Del_Rng as Range
Set oSave_Del_Rng = Range(sDelivery_Rng)

Help!


.

Saving Range Locatoin into a Variable
 

Hi Dale,

Sorry, but I'm relatively new to VBA. I tried the
following
Set oSave_Del_Rng.Address = Range(sDelivery_Rng)
which would not fly.

Your code was OK as is, it was just that the variable was not what you
thought it was. Since you declared it as Range, it was actually a range
object. So by using this at the end of your original code:

MsgBox oSave_Del_Rng.Address

the range address would be displayed in a messagebox.

Bob's code is more straightforward if you only want the Address property and
don't need any other properties of the Range you're referring to (Value, Row
#, Column #, etc). If that's the case, you can do it in a one-liner:

MsgBox Range(sDelivery_Rng).Address

Hopefully that clears things up a bit.

Regards,

Jake Marx
MS MVP - Excel


-----Original Message-----
Hi Dale,

Since you declare oSave_Del_Rng as a Range object (and
set it using Set ...
= ...), the object variable contains a reference to the
Range object you set
it to [in this case, oSave_Del_Rng is the same as typing
Range("Q215")].
Since the default property of a Range object is Value,
you will get the
value of the range Q215 when you refer to your
variable. To get the
address, you can use the Address property:

Debug.Print oSave_Del_Rng.Address

Regards,

Jake Marx
MS MVP - Excel


"Dale Cox" wrote in message
...
The following code executes just fine. The value moved
into oSave_Del_Rng, however, is the value in the Cell
Q215. I need the cell address, not the value of the
cell,
moved into the variable.
Const sDelivery_Rng = "Q215"
Dim oSave_Del_Rng as Range
Set oSave_Del_Rng = Range(sDelivery_Rng)

Help!


.

Saving Range Locatoin into a Variable
 

Well that's a good reason, and congrats.

I hope we will be seeing more of you, I've always felt you add to the group.

All the best

Bob

"Jake Marx" wrote in message
...
Hi Bob,

"Bob Phillips" wrote in message
...
Jake,

You're back! Where have you been? I guess you must have a life<vbg.

Yeah, life has been taking up a lot of my time lately. <g A new baby and
job are the biggest time consumers right now. But they're definitely
worth
it...at least the baby is. Hopefully, I'll be able to start spending some
more time here in the upcoming months.

Thanks,

Jake Marx
MS MVP - Excel