Offset and loop autofill formula until empty cells

[TheBaron26]

Hi,

I'm fairly new to VBA and am looking to write some code to autofill a formula.

I have the following code which works for column "I", but need to expand it to loop round until it finds the last column with data, offsetting by 2 columns each time (so start at column I, row 2, then column K, row 2, then column m, row 2 etc).

Sub Autofill()
Dim LastRow As Long, j As Long
 
With Sheets("Data Dump")
 
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
 
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-1]<>"""",R1C[-1]<=TODAY()),RC[-1]-RC[-3],"""")"
Range("I2").Select
Range("I2").Autofill Destination:=Range("I2:I" & LastRow)
 
End With
 
End Sub

Hope this makes sense and any pointers would be greatly appreciated!

regards

R

 

[VoG]

Try

Sub Autofill()
Dim LastRow As Long, LC As Long
 
With Sheets("Data Dump")
 
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
     LC = .Cells(1, Columns.Count).End(xlToLeft).Column + 2
    .Range(.Cells(1, LC), .Cells(LastRow, LC)).FormulaR1C1 = "=IF(AND(RC[-1]<>"""",R1C[-1]<=TODAY()),RC[-1]-RC[-3],"""")"
 
End With
 
End Sub

 

[TheBaron26]

Hi,

Many thanks for the prompt reply - the code does work but it populates columns after the last row of data. I need it to populate column I, then repeat until the last column of data is found.....

Many thanks,

R

 

[VoG]

Maybe

Sub Autofill()
Dim LastRow As Long, LC As Long
 
With Sheets("Data Dump")
 
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
     LC = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Range(.Cells(2, 9), .Cells(LastRow, LC)).FormulaR1C1 = "=IF(AND(RC[-1]<>"""",R1C[-1]<=TODAY()),RC[-1]-RC[-3],"""")"
 
End With
 
End Sub

 

[TheBaron26]

Hi Peter,

Again, it works, but the formula is not being offset: the formula appears in adjacent columns....i need it to populate columns I, K, M, O, Q, etc, but the columns I,J,K,L,M, are being popluated....

regards,

R

 

[VoG]

Try

Sub Autofill()
Dim LastRow As Long, LC As Long, j As Long
 
With Sheets("Data Dump")
 
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
     LC = .Cells(1, Columns.Count).End(xlToLeft).Column
     For j = 9 To LC Step 2
        .Range(.Cells(2, j), .Cells(LastRow, j)).FormulaR1C1 = "=IF(AND(RC[-1]<>"""",R1C[-1]<=TODAY()),RC[-1]-RC[-3],"""")"
    Next j
End With
 
End Sub

 

[TheBaron26]

Thank you so much for your help Peter, this code works perfectly!

Many many thanks,

R