Non-Sequential Count

[Brian]

I know normally to do a count one would use

For count = 1 To 4
Next count

But what if I don't want to do 1-4, and I wanted to do like 1,2,5-8?
What would I use then?

 

[Gary Keramidas]

a couple of ways
Sub test()
Dim i As Long
For i = 1 To 8
If i < 3 Or i > 4 Then
MsgBox i
End If
Next
End Sub
Sub test2()
Dim i As Long
Dim arr As Variant
arr = Array(1, 2, 5, 6, 7, 8)
For i = LBound(arr) To UBound(arr)
MsgBox arr(i)
Next
End Sub

 

[Joel]

num = array(1,2,5,7,8)
For each count in num

 

[Brian]

Joel thanks for the help over the past few days. This worked fine when I
explicitly stated the numbers; however, when I do something like the
following I get Subscript Out of Range error:

Dim strABC as String, lResult as Long

strABC = "1,2,5,6,7,8"
num=array(strABC)
For each count in num
lResult = lResult + Worksheets(count).Range("A1").Value
Next count
MsgBox lResult

I'm using criteria to build the string strABC, so this is just an example,
and I can't explicitly say what numbers I want.

Thanks again.

 

[Gary Keramidas]

you array is one element "1,2,5,6,7,8"
instead of separate elements

if you're doing it like that, you'd have to use something like the split
function to separate the values
split(num(0),",")

try this with the code you just posted:
add this line
Dim num As Variant

comment out the for next loop lines

then click debug, add a watch and type in num
create a breakpoint on the message box line so the code stops

click the + sign next to num in the watch window. you have one element

once you've seen it, stop the code

now, do the same procedure with the code i posted below, you will have 6
elements


you're better off doing something like this

Sub test()
Dim i As Long, lResult As Long
Dim num As Variant
num = Array(1, 2, 5, 6, 7, 8)
For i = LBound(num) To UBound(num)
lResult = lResult + Worksheets(num(i)).Range("A1").Value
Debug.Print lResult
Next
MsgBox lResult
End Sub