Selective Range extraction depends on the cell outcome

[Elton Law]

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?

 

[Chip Pearson]

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)

 

[Jacob Skaria]

Try the below

Sub Macro()
Range(String(3, Chr(64 + Range("A5")))).Select
End Sub

If this post helps click Yes

 

[Elton Law]

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]

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

 

[Elton Law]

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]

'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

 

[Elton Law]

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 (e-mail address removed)

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

 

[J_Knowles]

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,

 

[Elton Law]

OOOh Man .... It is my mistake ....
Yes, should be E1 ................
Now works ... Thanks so mcuh!!!!!!

 

[Jacob Skaria]

In your original post you have mentioned
If this post helps click Yes