VBA - For each loop on an array...

[RobbieC]

Hi there, I want to run a 'For each loop' on an array.

I have the following code which will do what I need for a continuous string of numbers:

For i = 1 to 26

 'do something with i

next

but in this new case I only want several numbers : 1, 4, 9, 10, 15, 17, 23 etc

is it possible to run my above code with something like:

For i = array(1, 4, 9, 10, 15, 17, 23)

 'do something with i

next

If you can point me in the right direction, I'd be very grateful.

Thanks

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG26May29
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] i [COLOR="Navy"]In[/COLOR] Array(1, 4, 9, 10, 15, 17, 23)
    Debug.Print i
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[farmerscott]

This code uses each element of the array and uses that number as a variable (ie rows, 1,4,9 etc).

Sub Loop_array()

Dim arr As Variant

Dim i As Long
Dim x As Long

arr = Array(1, 4, 9, 10, 15, 17, 19)



For i = LBound(arr) To UBound(arr)

x = arr(i)


Cells(x, 1).Value = 45

Next i

End Sub

 

[RobbieC]

Brilliant Mick, works perfectly... that's gonna save me a whole load of time and code reproduction.

Thanks very much!