Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving as variable file name | Excel Discussion (Misc queries) | |||
variable range | New Users to Excel | |||
Variable range in VB | Excel Discussion (Misc queries) | |||
Variable in Range | Excel Discussion (Misc queries) | |||
Saving Registry variable to ini file | Excel Programming |