VBA: Move thru columns with an array

[FryGirl]

I'm trying to cycle thru some columns, but can't get this array correctly implemented.

It would be easy to use a For loop with a step of say 4, but unfortunately the last column is 5 columns to the right.

Ultimately this is column BL, BP, and BU.

[FONT=Times New Roman][SIZE=3][COLOR=#000000]Sub AverageCells()[/COLOR][/SIZE][/FONT]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]    Dim x As Long[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]    Dim c As Range[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]    Dim arr() As Variant: arr = Array(64, 68,73)[/FONT][/COLOR][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]    WithActiveSheet[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]        For x = LBound(arr) To UBound(arr)[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]            For Each c In .Range(.Cells(1, x),.Cells(.Rows.Count, x).End(xlUp))[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]                c.Value = c.Value / 12[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]            Next c[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]        Next x[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]    End With[/FONT][/COLOR][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]

 

[Fluff]

It needs to be like

[SIZE=3][COLOR=#000000][FONT=Times New Roman]For Each c In .Range(.Cells(1, arr(x)),.Cells(.Rows.Count, arr(x)).End(xlUp))[/FONT][/COLOR][/SIZE]

 

[steve the fish]

Lbound(arr) will be 0 in this case so your For line will be trying to say .Cells(1,0) which is no good. You need to use arr(x) so that line could be:

For Each c In .Range(.Cells(1, arr(x)), .Cells(.Rows.Count, arr(x)).End(xlUp))

 

[FryGirl]

This works great. Thank you.

 

[Fluff]

Glad we could help & thanks for the feedback