Inconsistent Autofill method of range class failure RRS feed

  • Question

  • Has anyone encountered a situation in which the same macro fails with Error 1004: "Autofill method of range class failed" some but not all of the time? Can anyone offer any advice as to how to troubleshoot or fix this?

    I have a sheet with headers in row 1 where I populate via VBA formulas in row 2 of several columns. About 50% of the time when I run the macro I get the Autofill method of range class failed error. What strike me as really weird is 100% of the time when I get the error if I click to debug it takes me to the offending line of code and then either hitting F8 or clicking the run button on the toolbar will successfully run the line causing the error and the rest of the macro (even though the same method is used just 2 lines later). It may be worth noting that each instance of the workbook is consistent, i.e.: once I've opened the workbook the macro will either run without throwing the error or fail every time (but still run successfully using F8 or the run button.

    Here's some simplified code, it may or may not give you the error, it only gives me the error about half the time.

    Option Explicit
         Public wsDataManager As Worksheet
    Public Function InitializeVariables()
         Set wsDataManager = Sheet1
    End Function
    Sub AutoFillTest()
    Dim lLastRow As Long
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
    With ActiveSheet
        lLastRow = .Cells(65000, 1).End(xlUp).Row
        If lLastRow < 2 Then
            lLastRow = 2
        End If
        .Range(.Cells(2, 2), .Cells(lLastRow, 3)).Clear
        .Cells(2, 2).Formula = "=A2"
        .Cells(2, 3).Formula = "=B2+100"
        If lLastRow > 2 Then
            .Range(.Cells(2, 2), .Cells(2, 2)).Select
    'THE NEXT LINE FAILS WHEN I RUN THE MACRO BUT RUNS IN THE VBA WINDOW (F8 OR RUN) Selection.AutoFill Destination:=.Range(.Cells(2, 2), .Cells(lLastRow, 2)) .Range(.Cells(2, 3), .Cells(2, 3)).Select
    'THE NEXT LINE WILL FAIL IF I COMMENT OUT THE PREVIOUS AUTOFILL BUT RUNS VIA RUN BUTTON EVEN WHEN THE PREVIOUS FAILS Selection.AutoFill Destination:=.Range(.Cells(2, 3), .Cells(lLastRow, 3)) End If .Cells(1, 1).Select End With 'ActiveSheet wsDataManager Application.EnableEvents = True Application.ScreenUpdating = True Application.DisplayAlerts = True Application.Calculation = xlAutomatic End Sub
    I've used this method of filling columns many times. I've never encountered an error that only happens some of the time. I kind of hope there's something subtle in the syntax that leaves this method vulnerable without causing outright failure but I'm not expert enough to know if that's even a thing that happens.

    Regardless, thanks in advance for any help or guidance you can offer.

    Tuesday, April 28, 2020 3:41 AM