creating a VBA variabel from multiple named worksheet ranges
 

Hi,

I have the following code which creates the VBA variable called VBA_A
from the named range rng_A:

VBA_A = [rng_A]

I have a rng_A for each currency, one per worksheet. Each one refers to
a column matrix and they are not necessarily of the same dimensions. So
for example, I have a range rng_AUSD for the US$ on one worksheet. I
wanted to change the above line to be able to select the correct range
depending on which worksheet I am on. To do this I have entered the
3-letter code for the currency in cell C1 on each worksheet. So I tried
the following:

Dim ccy As String
ccy = ActiveSheet.Range("C1").Value
VBA_A = [rng_A & ccy]

I find this gives me Error 2029. It steps over the code but the data is
not in the variable when I "add to the Watch". Has anyone got any ideas
what's going wrong?

Thanks very much,

ed


--
brachistochrone
------------------------------------------------------------------------
brachistochrone's Profile: http://www.excelforum.com/member.php...o&userid=34274
View this thread: http://www.excelforum.com/showthread...hreadid=547187

creating a VBA variabel from multiple named worksheet ranges
 

Dim ccy As String

ccy = ActiveSheet.Range("C1").Value
VBA_A = Range("rng_A" & ccy).Value



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"brachistochrone"
<brachistochrone.28ops0_1149100203.5993@excelfor um-nospam.com wrote in
message news:brachistochrone.28ops0_1149100203.5993@excelf orum-nospam.com...

Hi,

I have the following code which creates the VBA variable called VBA_A
from the named range rng_A:

VBA_A = [rng_A]

I have a rng_A for each currency, one per worksheet. Each one refers to
a column matrix and they are not necessarily of the same dimensions. So
for example, I have a range rng_AUSD for the US$ on one worksheet. I
wanted to change the above line to be able to select the correct range
depending on which worksheet I am on. To do this I have entered the
3-letter code for the currency in cell C1 on each worksheet. So I tried
the following:

Dim ccy As String
ccy = ActiveSheet.Range("C1").Value
VBA_A = [rng_A & ccy]

I find this gives me Error 2029. It steps over the code but the data is
not in the variable when I "add to the Watch". Has anyone got any ideas
what's going wrong?

Thanks very much,

ed


--
brachistochrone
------------------------------------------------------------------------
brachistochrone's Profile:
http://www.excelforum.com/member.php...o&userid=34274
View this thread: http://www.excelforum.com/showthread...hreadid=547187

creating a VBA variabel from multiple named worksheet ranges
 

Thanks very much. It works!

Ed


--
brachistochrone
------------------------------------------------------------------------
brachistochrone's Profile: http://www.excelforum.com/member.php...o&userid=34274
View this thread: http://www.excelforum.com/showthread...hreadid=547187