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"
<[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[brachistochrone]

Thanks very much. It works!

Ed