| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Any way to loop this in VBA?
If ComboBox3 = 1 Then
Range("R199").Select ElseIf ComboBox3 = 2 Then Range("R227").Select ElseIf ComboBox3 = 3 Then Range("R255").Select ElseIf ComboBox3 = 4 Then Range("R283").Select ElseIf ComboBox3 = 5 Then Range("R311").Select ElseIf ComboBox3 = 6 Then Range("R339").Select ElseIf ComboBox3 = 7 Then Range("R367").Select ElseIf ComboBox3 = 8 Then Range("R395").Select ElseIf ComboBox3 = 9 Then Range("R423").Select ElseIf ComboBox3 = 10 Then Range("R451").Select ElseIf ComboBox3 = 11 Then Range("R479").Select ElseIf ComboBox3 = 12 Then Range("R507").Select ElseIf ComboBox3 = 13 Then Range("R535").Select ElseIf ComboBox3 = 14 Then Range("R563").Select ElseIf ComboBox3 = 15 Then Range("R591").Select ElseIf ComboBox3 = 16 Then Range("R619").Select ElseIf ComboBox3 = 17 Then Range("R647").Select ElseIf ComboBox3 = 18 Then Range("R675").Select ElseIf ComboBox3 = 19 Then Range("R703").Select ElseIf ComboBox3 = 20 Then Range("R731").Select ElseIf ComboBox3 = 21 Then Range("R759").Select ElseIf ComboBox3 = 22 Then Range("R787").Select ElseIf ComboBox3 = 23 Then Range("R815").Select ElseIf ComboBox3 = 24 Then Range("R843").Select ElseIf ComboBox3 = 25 Then Range("R871").Select ElseIf ComboBox3 = 26 Then Range("R899").Select Thanks in advance. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Any way to loop this in VBA?
You can set that code to trigger at an "On Change" or maybe a "MouseUp"
event. Is that the kind of loop you need? PS Don't forget your End If line! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Any way to loop this in VBA?
It kind of looks to me like a good candidate for a CASE statement. Go into
Excel's VBA help window and look up SELECT CASE STATEMENT. Alternatively, I haven't examined every case but it looks like your Range statements just go up in increments of 28? If so, then try: X = 171 + 28*ComboBox3 Range("R" & X).select Bill -------------------------- On 22 Feb 2006 07:44:46 -0800, Bill (Unique as my name) wrote: If ComboBox3 = 1 Then Range("R199").Select ElseIf ComboBox3 = 2 Then Range("R227").Select ElseIf ComboBox3 = 3 Then Range("R255").Select ElseIf ComboBox3 = 4 Then Range("R283").Select ElseIf ComboBox3 = 5 Then Range("R311").Select ElseIf ComboBox3 = 6 Then Range("R339").Select ElseIf ComboBox3 = 7 Then Range("R367").Select ElseIf ComboBox3 = 8 Then Range("R395").Select ElseIf ComboBox3 = 9 Then Range("R423").Select ElseIf ComboBox3 = 10 Then Range("R451").Select ElseIf ComboBox3 = 11 Then Range("R479").Select ElseIf ComboBox3 = 12 Then Range("R507").Select ElseIf ComboBox3 = 13 Then Range("R535").Select ElseIf ComboBox3 = 14 Then Range("R563").Select ElseIf ComboBox3 = 15 Then Range("R591").Select ElseIf ComboBox3 = 16 Then Range("R619").Select ElseIf ComboBox3 = 17 Then Range("R647").Select ElseIf ComboBox3 = 18 Then Range("R675").Select ElseIf ComboBox3 = 19 Then Range("R703").Select ElseIf ComboBox3 = 20 Then Range("R731").Select ElseIf ComboBox3 = 21 Then Range("R759").Select ElseIf ComboBox3 = 22 Then Range("R787").Select ElseIf ComboBox3 = 23 Then Range("R815").Select ElseIf ComboBox3 = 24 Then Range("R843").Select ElseIf ComboBox3 = 25 Then Range("R871").Select ElseIf ComboBox3 = 26 Then Range("R899").Select Thanks in advance. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Any way to loop this in VBA?
It looks like there's 28 rows between each one.
range("R" & 199 + 28 * (combobox3-1)).select "Bill (Unique as my name)" wrote: If ComboBox3 = 1 Then Range("R199").Select ElseIf ComboBox3 = 2 Then Range("R227").Select ElseIf ComboBox3 = 3 Then Range("R255").Select ElseIf ComboBox3 = 4 Then Range("R283").Select ElseIf ComboBox3 = 5 Then Range("R311").Select ElseIf ComboBox3 = 6 Then Range("R339").Select ElseIf ComboBox3 = 7 Then Range("R367").Select ElseIf ComboBox3 = 8 Then Range("R395").Select ElseIf ComboBox3 = 9 Then Range("R423").Select ElseIf ComboBox3 = 10 Then Range("R451").Select ElseIf ComboBox3 = 11 Then Range("R479").Select ElseIf ComboBox3 = 12 Then Range("R507").Select ElseIf ComboBox3 = 13 Then Range("R535").Select ElseIf ComboBox3 = 14 Then Range("R563").Select ElseIf ComboBox3 = 15 Then Range("R591").Select ElseIf ComboBox3 = 16 Then Range("R619").Select ElseIf ComboBox3 = 17 Then Range("R647").Select ElseIf ComboBox3 = 18 Then Range("R675").Select ElseIf ComboBox3 = 19 Then Range("R703").Select ElseIf ComboBox3 = 20 Then Range("R731").Select ElseIf ComboBox3 = 21 Then Range("R759").Select ElseIf ComboBox3 = 22 Then Range("R787").Select ElseIf ComboBox3 = 23 Then Range("R815").Select ElseIf ComboBox3 = 24 Then Range("R843").Select ElseIf ComboBox3 = 25 Then Range("R871").Select ElseIf ComboBox3 = 26 Then Range("R899").Select Thanks in advance. -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Any way to loop this in VBA?
Alternatively, you can first store the ranges in an array. This can be
usefull if they're not a mathematical increment (like +28). Option Base 1 'so that the first array index is 1, needs to be placed in the very beginning of the module code Sub SelectTheRange() Dim arrRanges() as String arrRanges = Array("R100","R12","R244","R512","R10:U10", .....) Range(arrRanges(ComboBox3)).Select End Sub |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Any way to loop this in VBA?
I don't know how to show my appreciation.
I am numb with bliss. You guys are just plain awesome. Thanks again for the help. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Loop gone crazy | Excel Discussion (Misc queries) | |||
| Do Loop | Excel Discussion (Misc queries) | |||
| Help with Do...Loop | Excel Discussion (Misc queries) | |||
| VB for excel, how do I loop through code | Excel Discussion (Misc queries) | |||
| loop trough e-mail address list to send task lists with outlook | Excel Discussion (Misc queries) | |||
Hybrid Mode
