Looping through multiple non consecutive columns

[ODrumea]

Hello,

I'm very new to VBA but do my best to search pre-existing solutions.
I have a spreadsheet with multiple columns. For each column, I have a formula in row 5 that I want copied down in the entire column below. The paste range would actually start with row 8.
A very plain but inefficient way to do that is to repeat the below code for all my columns (change col reference in range) but they're quite a few and it takes ages to run:

Sub Macro3()

Dim last_row As Long

Range("H5").Select
Selection.Copy
Range("H8").Select
ActiveSheet.Paste
Application.CutCopyMode = False
last_row = Cells(Rows.Count, 6).End(xlUp).Row
Range("H8").AutoFill Destination:=Range("h8:h" & last_row)

End Sub

I'm looking for a way to loop this action through this array of columns: (8, 11, 14, 17, 20, 23, 27, 30, 33, 36, 39, 42, 45, 47). I've read on For Each statements, but I don't know how to build in the actually code for the formula to be copied from row 5 (above each column header) to the rest of the column, for each column.

Any help would be much appreciated!

Thanks,
Oana

 

[Snakehips]

@ODrumea Welcome

Can you confirm that column numbers stated are correct and that it is not always an increment of 3 ?

 

[ODrumea]

@ODrumea Welcome

Can you confirm that column numbers stated are correct and that it is not always an increment of 3 ?

Hello,
Confirming the actual col numbers are correct. They are an increment of 3 due to me having a repeating structure of the data.
Thanks!

 

[Snakehips]

Just double checking...
23 , 27. is increment of 4
45, 47. is increment of 2
All others are 3

 

[Snakehips]

If your stated column numbers are correct then try this on a backed up file.

Sub Formula_Repeat()
Dim lr As Long
Dim col As Long

lr = Cells(Rows.Count, 6).End(xlUp).Row

For col = 8 To 45 Step 3
    If col = 26 Then col = 27
    If col = 45 Then col = 47

    Range(Cells(8, col), Cells(lr, col)).Formula = Cells(5, col).Formula
Next col

End Sub

 

[ODrumea]

If your stated column numbers are correct then try this on a backed up file.

Sub Formula_Repeat()
Dim lr As Long
Dim col As Long

lr = Cells(Rows.Count, 6).End(xlUp).Row

For col = 8 To 45 Step 3
    If col = 26 Then col = 27
    If col = 45 Then col = 47

    Range(Cells(8, col), Cells(lr, col)).Formula = Cells(5, col).Formula
Next col

End Sub

Thank you so much! It worked like a charm!