Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selective Range extraction depends on the cell outcome
Dear Expert,
I am writing a marco but get stuck in one part. Address A1 to C2 is defined the name as AAA Address D1 to F2 is defined the name as BBB Address G1 to I2 is defined the names as CCC Address J1 to L2 is defined the name as DDD ....... Say Cell A5 has an answer "1". Then the marco will automatically select AAA (A1 to C2). Say Cell A5 has an answer of "2". Then the marco will select BBB (D1 to F2). Etc Etc .. A5 is a formula and the answer can be 1 to 12 ..... Then help me to select each range depending on the answer in cell A5. Is that feasbile? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selective Range extraction depends on the cell outcome
Try something like the following code:
Dim V(1) As Long Dim RangeNames As Variant RangeNames = Array("AAA_", "BBB_", "CCC_", "DDD_") On Error Resume Next Range(RangeNames(Range("A5").Value - Abs(LBound(V) = 0))).Select First of all, I recommend that you choose different range names than those you use in your example. Because XL2007 has 16K columns rather than 256 as is the case in earlier versions, names that are legal in XL2003, such as AAA, refer to columns in XL2007 and shouldn't be used as names. You should use names that will not cause ambiguity or conflicts in XL2007. Even if you aren't using XL2007 now, you will be sometime in the future and incompatible range names will come back to bite you in the ass later. The Array method returns an array from the series of input strings. If you have Option Base 0 or no Option Base statement at the top of your code module, the first element of the array is index 0. If you have Option Base 1 at the top of the module, the first element of the array is index 1. The " - Abs(LBound(V) = 0)" piece of the code automatically adjusts the index so that the code will always work on a 1-based index into the array. While the variable V(1) isn't really used in the code, it serves are a test for the array base index dictated by the module settings. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 6 Nov 2009 07:56:02 -0800, Elton Law wrote: Dear Expert, I am writing a marco but get stuck in one part. Address A1 to C2 is defined the name as AAA Address D1 to F2 is defined the name as BBB Address G1 to I2 is defined the names as CCC Address J1 to L2 is defined the name as DDD ...... Say Cell A5 has an answer "1". Then the marco will automatically select AAA (A1 to C2). Say Cell A5 has an answer of "2". Then the marco will select BBB (D1 to F2). Etc Etc .. A5 is a formula and the answer can be 1 to 12 ..... Then help me to select each range depending on the answer in cell A5. Is that feasbile? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selective Range extraction depends on the cell outcome
Try the below
Sub Macro() Range(String(3, Chr(64 + Range("A5")))).Select End Sub If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Dear Expert, I am writing a marco but get stuck in one part. Address A1 to C2 is defined the name as AAA Address D1 to F2 is defined the name as BBB Address G1 to I2 is defined the names as CCC Address J1 to L2 is defined the name as DDD ...... Say Cell A5 has an answer "1". Then the marco will automatically select AAA (A1 to C2). Say Cell A5 has an answer of "2". Then the marco will select BBB (D1 to F2). Etc Etc .. A5 is a formula and the answer can be 1 to 12 ..... Then help me to select each range depending on the answer in cell A5. Is that feasbile? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selective Range extraction depends on the cell outcome
Hi Jacob,
Look like cannot make it .... It cannot debug .... Col A Col B Col C Col D Col E Kammi 12 1 Jimmy 34 Jasmine 26 Fanny 27 Tammy 89 Crystal 12 Tinny 17 Peter 34 Mary 27 Dion 68 Wella 32 Palla 11 Juno 29 Mike 20 Say a real example above ... If A5 is 1, then select Kammi, Jimmy, Jasmine, 12, 34, 26, all which are defined as range "AAA". If A5 is 2, then select range from fanny, tammy, crystal,27,89,12, all which are defined as range "BBB" before. There are 13 named ranges to be selected and that are depending on the variables in cell A5 (from 1 to 13). Hope you can help ... Thanks indeed... "Jacob Skaria" wrote: Try the below Sub Macro() Range(String(3, Chr(64 + Range("A5")))).Select End Sub If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Dear Expert, I am writing a marco but get stuck in one part. Address A1 to C2 is defined the name as AAA Address D1 to F2 is defined the name as BBB Address G1 to I2 is defined the names as CCC Address J1 to L2 is defined the name as DDD ...... Say Cell A5 has an answer "1". Then the marco will automatically select AAA (A1 to C2). Say Cell A5 has an answer of "2". Then the marco will select BBB (D1 to F2). Etc Etc .. A5 is a formula and the answer can be 1 to 12 ..... Then help me to select each range depending on the answer in cell A5. Is that feasbile? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selective Range extraction depends on the cell outcome
Create any 3 named ranges AAA,BBB,CCC in the active sheet and try running the
macro on the activesheet itself....It works for me... If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Hi Jacob, Look like cannot make it .... It cannot debug .... Col A Col B Col C Col D Col E Kammi 12 1 Jimmy 34 Jasmine 26 Fanny 27 Tammy 89 Crystal 12 Tinny 17 Peter 34 Mary 27 Dion 68 Wella 32 Palla 11 Juno 29 Mike 20 Say a real example above ... If A5 is 1, then select Kammi, Jimmy, Jasmine, 12, 34, 26, all which are defined as range "AAA". If A5 is 2, then select range from fanny, tammy, crystal,27,89,12, all which are defined as range "BBB" before. There are 13 named ranges to be selected and that are depending on the variables in cell A5 (from 1 to 13). Hope you can help ... Thanks indeed... "Jacob Skaria" wrote: Try the below Sub Macro() Range(String(3, Chr(64 + Range("A5")))).Select End Sub If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Dear Expert, I am writing a marco but get stuck in one part. Address A1 to C2 is defined the name as AAA Address D1 to F2 is defined the name as BBB Address G1 to I2 is defined the names as CCC Address J1 to L2 is defined the name as DDD ...... Say Cell A5 has an answer "1". Then the marco will automatically select AAA (A1 to C2). Say Cell A5 has an answer of "2". Then the marco will select BBB (D1 to F2). Etc Etc .. A5 is a formula and the answer can be 1 to 12 ..... Then help me to select each range depending on the answer in cell A5. Is that feasbile? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selective Range extraction depends on the cell outcome
Hi Jacob,
I am using Excel 2007, but frankly speaking it does not work in my PC. To be honest, how does your script "Range(String(3, Chr(64 + Range("A5")))).Select" associate number 1 in cell A5 with range AAA and number 2 in cell A5 links to BBB and etc etc for 13 for MMM ...? I had right click the range A1 to B3 and choose "name the range" to AAA with scope workbook. I think all are defined correctly. Can you tell me more what is wrong so that I can fine-tune it? "Jacob Skaria" wrote: Create any 3 named ranges AAA,BBB,CCC in the active sheet and try running the macro on the activesheet itself....It works for me... If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Hi Jacob, Look like cannot make it .... It cannot debug .... Col A Col B Col C Col D Col E Kammi 12 1 Jimmy 34 Jasmine 26 Fanny 27 Tammy 89 Crystal 12 Tinny 17 Peter 34 Mary 27 Dion 68 Wella 32 Palla 11 Juno 29 Mike 20 Say a real example above ... If A5 is 1, then select Kammi, Jimmy, Jasmine, 12, 34, 26, all which are defined as range "AAA". If A5 is 2, then select range from fanny, tammy, crystal,27,89,12, all which are defined as range "BBB" before. There are 13 named ranges to be selected and that are depending on the variables in cell A5 (from 1 to 13). Hope you can help ... Thanks indeed... "Jacob Skaria" wrote: Try the below Sub Macro() Range(String(3, Chr(64 + Range("A5")))).Select End Sub If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Dear Expert, I am writing a marco but get stuck in one part. Address A1 to C2 is defined the name as AAA Address D1 to F2 is defined the name as BBB Address G1 to I2 is defined the names as CCC Address J1 to L2 is defined the name as DDD ...... Say Cell A5 has an answer "1". Then the marco will automatically select AAA (A1 to C2). Say Cell A5 has an answer of "2". Then the marco will select BBB (D1 to F2). Etc Etc .. A5 is a formula and the answer can be 1 to 12 ..... Then help me to select each range depending on the answer in cell A5. Is that feasbile? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selective Range extraction depends on the cell outcome
'Try the below which will return A
Msgbox Chr(65) 'the below would return AAA Msgbox String(3,Chr(65)) So when A5 = 1 that means 'Range("aaa").select OR Range(String(3, Chr(64 + Range("A5")))).Select If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Hi Jacob, I am using Excel 2007, but frankly speaking it does not work in my PC. To be honest, how does your script "Range(String(3, Chr(64 + Range("A5")))).Select" associate number 1 in cell A5 with range AAA and number 2 in cell A5 links to BBB and etc etc for 13 for MMM ...? I had right click the range A1 to B3 and choose "name the range" to AAA with scope workbook. I think all are defined correctly. Can you tell me more what is wrong so that I can fine-tune it? "Jacob Skaria" wrote: Create any 3 named ranges AAA,BBB,CCC in the active sheet and try running the macro on the activesheet itself....It works for me... If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Hi Jacob, Look like cannot make it .... It cannot debug .... Col A Col B Col C Col D Col E Kammi 12 1 Jimmy 34 Jasmine 26 Fanny 27 Tammy 89 Crystal 12 Tinny 17 Peter 34 Mary 27 Dion 68 Wella 32 Palla 11 Juno 29 Mike 20 Say a real example above ... If A5 is 1, then select Kammi, Jimmy, Jasmine, 12, 34, 26, all which are defined as range "AAA". If A5 is 2, then select range from fanny, tammy, crystal,27,89,12, all which are defined as range "BBB" before. There are 13 named ranges to be selected and that are depending on the variables in cell A5 (from 1 to 13). Hope you can help ... Thanks indeed... "Jacob Skaria" wrote: Try the below Sub Macro() Range(String(3, Chr(64 + Range("A5")))).Select End Sub If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Dear Expert, I am writing a marco but get stuck in one part. Address A1 to C2 is defined the name as AAA Address D1 to F2 is defined the name as BBB Address G1 to I2 is defined the names as CCC Address J1 to L2 is defined the name as DDD ...... Say Cell A5 has an answer "1". Then the marco will automatically select AAA (A1 to C2). Say Cell A5 has an answer of "2". Then the marco will select BBB (D1 to F2). Etc Etc .. A5 is a formula and the answer can be 1 to 12 ..... Then help me to select each range depending on the answer in cell A5. Is that feasbile? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selective Range extraction depends on the cell outcome
In your example cell E1 is the trigger for selecting the groupings (not cell
A5 as you noted). So, taking Jacob Skaria's code and making this change will get you going. Be sure you have defined the range names AAA, BBB, CCC, etc, Sub Macro() Range(String(3, Chr(64 + Range("E1")))).Select End Sub HTH, -- Data Hog "Elton Law" wrote: Hi Jacob, Look like cannot make it .... It cannot debug .... Col A Col B Col C Col D Col E Kammi 12 1 Jimmy 34 Jasmine 26 Fanny 27 Tammy 89 Crystal 12 Tinny 17 Peter 34 Mary 27 Dion 68 Wella 32 Palla 11 Juno 29 Mike 20 Say a real example above ... If A5 is 1, then select Kammi, Jimmy, Jasmine, 12, 34, 26, all which are defined as range "AAA". If A5 is 2, then select range from fanny, tammy, crystal,27,89,12, all which are defined as range "BBB" before. There are 13 named ranges to be selected and that are depending on the variables in cell A5 (from 1 to 13). Hope you can help ... Thanks indeed... "Jacob Skaria" wrote: Try the below Sub Macro() Range(String(3, Chr(64 + Range("A5")))).Select End Sub If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Dear Expert, I am writing a marco but get stuck in one part. Address A1 to C2 is defined the name as AAA Address D1 to F2 is defined the name as BBB Address G1 to I2 is defined the names as CCC Address J1 to L2 is defined the name as DDD ...... Say Cell A5 has an answer "1". Then the marco will automatically select AAA (A1 to C2). Say Cell A5 has an answer of "2". Then the marco will select BBB (D1 to F2). Etc Etc .. A5 is a formula and the answer can be 1 to 12 ..... Then help me to select each range depending on the answer in cell A5. Is that feasbile? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selective Range extraction depends on the cell outcome
OOOh Man .... It is my mistake ....
Yes, should be E1 ................ Now works ... Thanks so mcuh!!!!!! "J_Knowles" wrote: In your example cell E1 is the trigger for selecting the groupings (not cell A5 as you noted). So, taking Jacob Skaria's code and making this change will get you going. Be sure you have defined the range names AAA, BBB, CCC, etc, Sub Macro() Range(String(3, Chr(64 + Range("E1")))).Select End Sub HTH, -- Data Hog "Elton Law" wrote: Hi Jacob, Look like cannot make it .... It cannot debug .... Col A Col B Col C Col D Col E Kammi 12 1 Jimmy 34 Jasmine 26 Fanny 27 Tammy 89 Crystal 12 Tinny 17 Peter 34 Mary 27 Dion 68 Wella 32 Palla 11 Juno 29 Mike 20 Say a real example above ... If A5 is 1, then select Kammi, Jimmy, Jasmine, 12, 34, 26, all which are defined as range "AAA". If A5 is 2, then select range from fanny, tammy, crystal,27,89,12, all which are defined as range "BBB" before. There are 13 named ranges to be selected and that are depending on the variables in cell A5 (from 1 to 13). Hope you can help ... Thanks indeed... "Jacob Skaria" wrote: Try the below Sub Macro() Range(String(3, Chr(64 + Range("A5")))).Select End Sub If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Dear Expert, I am writing a marco but get stuck in one part. Address A1 to C2 is defined the name as AAA Address D1 to F2 is defined the name as BBB Address G1 to I2 is defined the names as CCC Address J1 to L2 is defined the name as DDD ...... Say Cell A5 has an answer "1". Then the marco will automatically select AAA (A1 to C2). Say Cell A5 has an answer of "2". Then the marco will select BBB (D1 to F2). Etc Etc .. A5 is a formula and the answer can be 1 to 12 ..... Then help me to select each range depending on the answer in cell A5. Is that feasbile? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selective Range extraction depends on the cell outcome
In your original post you have mentioned
A5 is a formula and the answer can be 1 to 12 ..... If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Hi Jacob, Thanks so muhc for your help ... I tried this ... MsgBox String(3, Chr(64 + Range("A5"))) But it does not pop up a msg box for AAA. It comes with error. So I think "Range(String(3, Chr(64 + Range("A5")))).Select" may not work becasue "String(3, Chr(64 + Range("A5")))" does not equal to AAA. Can you send your applicable file with VB sript to me please? My Email is So make it simple .... Whenever see "1", in cell A5, select the range A1:C3 Whenever see "2", in cell A5, select the range D1:F3 Name can be skipped ... Thanks indeed Elton "Jacob Skaria" wrote: 'Try the below which will return A Msgbox Chr(65) 'the below would return AAA Msgbox String(3,Chr(65)) So when A5 = 1 that means 'Range("aaa").select OR Range(String(3, Chr(64 + Range("A5")))).Select If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Hi Jacob, I am using Excel 2007, but frankly speaking it does not work in my PC. To be honest, how does your script "Range(String(3, Chr(64 + Range("A5")))).Select" associate number 1 in cell A5 with range AAA and number 2 in cell A5 links to BBB and etc etc for 13 for MMM ...? I had right click the range A1 to B3 and choose "name the range" to AAA with scope workbook. I think all are defined correctly. Can you tell me more what is wrong so that I can fine-tune it? "Jacob Skaria" wrote: Create any 3 named ranges AAA,BBB,CCC in the active sheet and try running the macro on the activesheet itself....It works for me... If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Hi Jacob, Look like cannot make it .... It cannot debug .... Col A Col B Col C Col D Col E Kammi 12 1 Jimmy 34 Jasmine 26 Fanny 27 Tammy 89 Crystal 12 Tinny 17 Peter 34 Mary 27 Dion 68 Wella 32 Palla 11 Juno 29 Mike 20 Say a real example above ... If A5 is 1, then select Kammi, Jimmy, Jasmine, 12, 34, 26, all which are defined as range "AAA". If A5 is 2, then select range from fanny, tammy, crystal,27,89,12, all which are defined as range "BBB" before. There are 13 named ranges to be selected and that are depending on the variables in cell A5 (from 1 to 13). Hope you can help ... Thanks indeed... "Jacob Skaria" wrote: Try the below Sub Macro() Range(String(3, Chr(64 + Range("A5")))).Select End Sub If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Dear Expert, I am writing a marco but get stuck in one part. Address A1 to C2 is defined the name as AAA Address D1 to F2 is defined the name as BBB Address G1 to I2 is defined the names as CCC Address J1 to L2 is defined the name as DDD ...... Say Cell A5 has an answer "1". Then the marco will automatically select AAA (A1 to C2). Say Cell A5 has an answer of "2". Then the marco will select BBB (D1 to F2). Etc Etc .. A5 is a formula and the answer can be 1 to 12 ..... Then help me to select each range depending on the answer in cell A5. Is that feasbile? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return Value from a Range (depends on input to determind month) | Excel Worksheet Functions | |||
Selective extraction of data from a table | Excel Worksheet Functions | |||
Formula depends on which cell contains max. | Excel Worksheet Functions | |||
Selective extraction of data | Excel Discussion (Misc queries) | |||
how to select range when no. of rows depends from text place ?? | Excel Programming |