I'm trying to create a For Loop that only selects some values but can't work out the Syntax, or if it's even possible?
I want it to be like
Dim i As Integer
For i = 1,3,8,15 Then
Do something
Next i
Any Ideas?
Add a comment
|
6 Answers
Try
Sub Demo()
Dim indexArr As Variant
Dim i As Long
indexArr = Array(1, 3, 8, 15)
For i = LBound(indexArr) To UBound(indexArr)
Debug.Print indexArr(i)
Next i
End Sub
Comments
You can do following:
Dim i as Variant
Dim iArray as Variant
iArray=Array(1,3,8,15)
For Each i In iArray
'Do Something
Next i
Cheers
1 Comment
mypetlion
I don't understand how this answers the question. OP wants to select only some values from the array. You've just demonstrated for loop syntax.
You can't.
If there is a pattern you can use Step
For i = 1 to 15 Step 2
Which will do 1,3,5,7,...
In this case since there is no pattern you will need to add an If or Select Case:
Dim i As Integer
For i = 1 to 15 Then
Select Case i
Case 1,3,8,15
'Do Something
End Select
Next i
Comments
You can use if statement:
Dim i As Integer
For i = 1 To 15 Then
If i = 1 or i = 3 or i = 8 or i = 15 Then
'Do something
End If
Next i
Hope this help.
Comments
This solution combines elements from the array solution above with the ease of use that I inferred was wanted by the OP. The array method requires looping through the array elements and then converting the array element back into a variable. This method skips 2 bits. Firstly, it skips the complex step logic of stepping through array elements. Secondly, it skips the step of converting an array element back into a usable variable.
Dim number_list as collection
set number_list = new collection
number_list.add 1
number_list.add 3
number_list.add 8
number_list.add 15
for each number in number_list
'Do something with number
Next number
3 Comments
dwirony
This works, but this seems like a big of a stretch - why bother declaring and creating a
Collection?
Nic3500
While this code may answer the question, providing additional context regarding how and/or why it solves the problem would improve the answer's long-term value.
bonkey
This solution combines elements from the array solution above with the ease of use that I inferred was wanted by the OP. The array method requires looping through the array elements and then converting the array element back into a variable. This method skips 2 bits. Firstly, it skips the complex step logic of stepping through array elements. Secondly, it skips the step of converting an array element back into a usable variable. Hope this helps.
This is even a simplification of the routine of Kedimir:
Original question:
Dim i As Integer
For i = 1,3,8,15 Then
' Do something
Next i
Solution:
Dim i As Variant
For Each i In Array(1, 3, 8, 15)
' Do something
Next i
For your information: the Variant is needed for the For Each loop, don't try to modify it into an integer, or the For Each loop won't be accepted.
I don't think that a more resembling solution is possible :-)
