ExcelBanter - Any way to loop this in VBA?

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Any way to loop this in VBA? (https://www.excelbanter.com/excel-discussion-misc-queries/73229-any-way-loop-vba.html)

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.

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!

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.

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

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

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.