I want to autofill multiple non-adjacent columns using VBA. I have tried a few things and am currently trying a for loop to loop through each column to autofill each successively. My For loop is not working.
Code:
Dim lRow As Long
lRow = Cells(Rows.Count, "A").End(xlUp).Row
Selection.Formula = "=IF(RC[2]<0,RC[1]*-1,RC[1])"
' Selection.AutoFill Destination:=ActiveCell.Range("A1:A" & lRow)
Dim cel As Range
For Each cel In Selection.Cells
Selection.AutoFill Destination:=cel.Range("A1:A" & lRow)
Next cel
I get
Run-time error 1004
AutoFill method of Range class failed
What's wrong with my for loop? Or, how else can I do this?
I left in the commented line as my first failed attempt
Please, try the next code not needing iteration:
Sub AutofilDiscRange()
Dim ws As Worksheet, lastR As Long
Set ws = ActiveSheet
lastR = ws.Range("A" & ws.rows.count).End(xlUp).row
Intersect(Selection.EntireColumn, ws.rows(Selection.row & ":" & lastR)).Formula = _
"=IF(RC[2]<0,RC[1]*-1,RC[1])"
End Sub