Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating dynamic ranges which are named. | Excel Worksheet Functions | |||
getting the sum of variabel ranges | Excel Worksheet Functions | |||
Creating a pivot table using all named ranges | Excel Programming | |||
Creating dymnamic named ranges | Excel Programming | |||
Creating Named Ranges in VBA | Excel Programming |