When running the code it return a - Autofill method of range class failed at last line of the code : VBA

Question

I'm new to VBA and I'm having trouble understanding where my code is falling down. I receive the message "Autofill method of Range class failed". Please Help me to solve

Code:

Sub SomeName()
    Dim lRow As Long
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim wkSheet As Worksheet
    Set wkSheet = wb.ActiveSheet
    
    lRow = wkSheet.Range("G" & wkSheet.Rows.Count).End(xlUp).Row
    
    Sheets("MilestoneStatus").Select
    
    lRow = wkSheet.Range("G" & wkSheet.Rows.Count).End(xlUp).Row
    
    wkSheet.Range("H1").Formula = "=IF(ISBLANK(RC[-1]),"""",IF(DAYS(TODAY(),RC[-1])<0,CONCATENATE(""Due in "",-DAYS(TODAY(),RC[-1]),""DAYS""),IF(DAYS(TODAY(),RC[-1])>0,CONCATENATE(""OVERDUE"",DAYS(TODAY(),RC[-1]),""DAYS""),""Due today"")))"
    
    With wkSheet.Range("H1")
        .Formula = Range("H1").Formula
        .AutoFill Destination:=Range.Offset(0, 0).Resize(lRow)
    End With
End Sub

Алексей Р · Accepted Answer · 2021-06-14T05:08:03.187

You don't need as many operations and variables to get the result you want, including .AutoFill. Try this code:

Sub SomeName()
    Dim lRow As Long
   
    With ThisWorkbook.Sheets("MilestoneStatus")
        lRow = .Range("G" & .Rows.Count).End(xlUp).Row
        .Range("H1").Resize(lRow).FormulaR1C1 = "=IF(ISBLANK(RC[-1]),"""",IF(DAYS(TODAY(),RC[-1])<0,CONCATENATE(""Due in "",-DAYS(TODAY(),RC[-1]),""DAYS""),IF(DAYS(TODAY(),RC[-1])>0,CONCATENATE(""OVERDUE"",DAYS(TODAY(),RC[-1]),""DAYS""),""Due today"")))"
    End With
End Sub

If you still need to use .AutoFill for some reason, you can do it this way:

Sub SomeName()
    Dim lRow As Long
    
    With ThisWorkbook.Sheets("MilestoneStatus")
        lRow = .Range("G" & .Rows.Count).End(xlUp).Row
        .Range("H1").FormulaR1C1 = "=IF(ISBLANK(RC[-1]),"""",IF(DAYS(TODAY(),RC[-1])<0,CONCATENATE(""Due in "",-DAYS(TODAY(),RC[-1]),""DAYS""),IF(DAYS(TODAY(),RC[-1])>0,CONCATENATE(""OVERDUE"",DAYS(TODAY(),RC[-1]),""DAYS""),""Due today"")))"
        .Range("H1").AutoFill Destination:=.Range("H1").Resize(lRow)
    End With
End Sub

I also recommend that you pay attention to How to avoid using Select in Excel VBA

When running the code it return a - Autofill method of range class failed at last line of the code : VBA

1 Answers1