excelvba

Autofill multiple columns in VBA

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

Selection: enter image description here

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

Solution

  • 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