pass named range address into variable
 

How can I create a string variable from a named range in a workbook?

ex:

Named range is at address: $D$11:$AB$11
Name is: data1
String I want passed into a variable is: "D11:AB11" or "$D$11:$AB$11"

Thanks
Rob

pass named range address into variable
 

One way is like this:

Range("data1").Address


--
Hope that helps.

Vergel Adriano


"okrob" wrote:

How can I create a string variable from a named range in a workbook?

ex:

Named range is at address: $D$11:$AB$11
Name is: data1
String I want passed into a variable is: "D11:AB11" or "$D$11:$AB$11"

Thanks
Rob

pass named range address into variable
 

Sub rob()
Dim s As String
s = Range("data1").Address
End Sub

--
Gary''s Student
gsnu200711

pass named range address into variable
 

s1= [data1].Address

"okrob" wrote:

How can I create a string variable from a named range in a workbook?

ex:

Named range is at address: $D$11:$AB$11
Name is: data1
String I want passed into a variable is: "D11:AB11" or "$D$11:$AB$11"

Thanks
Rob

pass named range address into variable
 

On Mar 21, 4:37 pm, Gleam wrote:
s1= [data1].Address



"okrob" wrote:
How can I create a string variable from a named range in a workbook?

ex:

Named range is at address: $D$11:$AB$11
Name is: data1
String I want passed into a variable is: "D11:AB11" or "$D$11:$AB$11"

Thanks
Rob- Hide quoted text -

- Show quoted text -

OK, Blonde moment...!!!
I forgot to mention that the range is in an unopened workbook...

pass named range address into variable
 

If the WB is not open, the normal way of dealing with data is ADO. Depends
on the structure of the data.

NickHK

"okrob" wrote in message
oups.com...
On Mar 21, 4:37 pm, Gleam wrote:
s1= [data1].Address



"okrob" wrote:
How can I create a string variable from a named range in a workbook?

ex:

Named range is at address: $D$11:$AB$11
Name is: data1
String I want passed into a variable is: "D11:AB11" or "$D$11:$AB$11"

Thanks
Rob- Hide quoted text -

- Show quoted text -

OK, Blonde moment...!!!
I forgot to mention that the range is in an unopened workbook...

pass named range address into variable
 

On Mar 21, 9:18 pm, "NickHK" wrote:
If the WB is not open, the normal way of dealing with data is ADO. Depends
on the structure of the data.

NickHK

"okrob" wrote in message

oups.com...



On Mar 21, 4:37 pm, Gleam wrote:
s1= [data1].Address

"okrob" wrote:
How can I create a string variable from a named range in a workbook?

ex:

Named range is at address: $D$11:$AB$11
Name is: data1
String I want passed into a variable is: "D11:AB11" or "$D$11:$AB$11"

Thanks
Rob- Hide quoted text -

- Show quoted text -

OK, Blonde moment...!!!
I forgot to mention that the range is in an unopened workbook...- Hide quoted text -

- Show quoted text -

That's what I figured, but I can't seem to get the address into a
variable. I can get the data, one cell at a time (not feasible
because the data moves about), but not the entire range... I can use
Ron de Bruin's code, but the data address is dynamic. Only the range
name stays the same. So, to use Ron's code, I have to modify it to
accept a string for the cell references. I have that done already,
but I can't get the string...

pass named range address into variable
 

On Mar 22, 7:36 am, "okrob" wrote:
On Mar 21, 9:18 pm, "NickHK" wrote:





If the WB is not open, the normal way of dealing with data is ADO. Depends
on the structure of the data.

NickHK

"okrob" wrote in message

roups.com...

On Mar 21, 4:37 pm, Gleam wrote:
s1= [data1].Address

"okrob" wrote:
How can I create a string variable from a named range in a workbook?

ex:

Named range is at address: $D$11:$AB$11
Name is: data1
String I want passed into a variable is: "D11:AB11" or "$D$11:$AB$11"

Thanks
Rob- Hide quoted text -

- Show quoted text -

OK, Blonde moment...!!!
I forgot to mention that the range is in an unopened workbook...- Hide quoted text -

- Show quoted text -

That's what I figured, but I can't seem to get the address into a
variable. I can get the data, one cell at a time (not feasible
because the data moves about), but not the entire range... I can use
Ron de Bruin's code, but the data address is dynamic. Only the range
name stays the same. So, to use Ron's code, I have to modify it to
accept a string for the cell references. I have that done already,
but I can't get the string...- Hide quoted text -

- Show quoted text -

FYI - I finally solved my problem. What I really wanted was to copy a
named range into a workbook without opening the source.
Taking what I have found on Ron's site, coupled with some creative
stuff found on the board here... I figured that all I really had to
do was come up with a way to write an array formula to my sheet.

Sub loadsheet()
Range("D11:AA11").FormulaArray = "C:MyPath\[File.xls]Sheet1'!
myrangename"
End Sub

I was under the (mistaken) impression that this type of formula needed
an R1C1 type reference, but as it turns out, as long as the range is
defined in the source workbook, you can refer to it in the array of
another workbook. Basically, it's like pasting links, but with a
named range instead of a single cell.

Maybe this is out there somewhere, but everywhere I looked, this
always referenced a single cell or a R1C1 type reference. So anyway,
I'm done...

Rob