creating a VBA variabel from multiple named worksheet ranges

[brachistochrone]

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

 

[Bob Phillips]

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]

Thanks very much. It works!

Ed