Multiple Ifs Select

[ManUBlueJay]

I have a very complicated Question. The only way around that I can see is using 7 If,s
I am happy with a simple VBA formula or a way to name the ranges
I have seven different ranges, and depending on the users input I want to select given ranges.
If users selects Option 1 I want to select 7 ranges. A1:A4, A10:A14,A20:A24, A30:A34, A40:A44, A50:A54, A60:A64, A70:A74
If users selects Option 2 I want to select 6 ranges. All the options except the last.
If users selects Option 3 I want to select 5 ranges. All the options except the last2.

All the way up to user selecting option 7 and only A1:A4 would need selecting
I can't make a table and use a lookup.
Any help would be appreciated

 

[jasonb75]

First question, which part is wrong? You have listed 8 ranges for option 1, not 7 as you have specified.

Second question, do you want to select the ranges to do something with them, or just to draw the users attention to the correct range? Selecting is the most pointless thing that you can do in vba as a step toward a different task.

 

[ManUBlueJay]

Apologies just 7 ranges.
I want to do some formatting but if I can get them recognized I can take it from there

 

[jasonb75]

So which one is wrong?

Book1 (version 1).xlsx
	A	B
1	Range 1	A1:A4
2	Range 2	A10:A14
3	Range 3	A20:A24
4	Range 4	A30:A34
5	Range 5	A40:A44
6	Range 6	A50:A54
7	Range 7	A60:A64
8	Range 8	A70:A74
Sheet3

 

[ManUBlueJay]

If you read my first description, If option 1 is selected what tells me to select all 8 ranges without 7 nesting ifs

 

[jasonb75]

I did read it and that's not what it says.

If users selects Option 1 I want to select 7 ranges.

I was trying to clarify if the error was that you had an extra range that shouldn't have been included, or if it should have said 8 instead of 7.

We are not mind readers, if you ask the wrong question then you should expect to get the wrong answer. This is my best guess from the mishmash of misinformation you have provided. I've tried to set it so that it will work with any number of ranges, but that doesn't necessarily mean that it will.

Sub lostmycrystalball()
Dim rng As Range, rng2 As Range, a As Long, c As Long
Set rng = Range("A1:A4, A10:A14,A20:A24, A30:A34, A40:A44, A50:A54, A60:A64, A70:A74")
c = rng.Areas.Count
a = c - InputBox("Enter option 1-" & c) + 1
Set rng2 = rng.Areas(a)
Do Until a = 1
        a = a - 1
        Set rng2 = Application.Union(rng2, rng.Areas(a))
Loop
rng2.Select
End Sub

 

[mikerickson]

Are these options Option Buttons or something else that the user enters? Might the user choose none of the options (resulting in A1:A4).
Finally, what are you doing with the chosen range? Are you working in a worksheet environment (where many functions don't perform well with discontinuous ranges) or in VBA?

 

[ManUBlueJay]

Your Crystalball was right on.
Like I said I am not that strong in VBA
How do I set the inputbox
It is B1

 

[jasonb75]

It is B1

If the option is in B1 then you don't need the inputbox. Simply change that line to

a = c - Range("B1").Value + 1

It is for reasons like this that questions with minimal and / or inaccurate information don't get answered as quickly as others. After Q & A tennis to try and verify the erroneous details, the solution provided needs a change straight away because of basic information (telling us that the option is in B1) not provided in the first post.

Also, see the questions asked by @mikerickson in post 7.

 

[ManUBlueJay]

Sorry About that.
Another need has arisen. . I need to now select the rest of the ranges but this time the first selection needs to start from the input in B1 plus