AutoFill method of Range Class failed despite no recent change in code

Question

I'm getting Error 1004 AutoFill method of Range Class failed for the the below code. I have ran the code multiple times before and there has been no issue. Not sure what's causing the bug all of a sudden.

Thanks.

    Chkstartrow = miscChecks.Cells(miscChecks.Rows.Count, ChktransIDcol.column).End(xlUp).Row + 1
    misclastCol = miscChecks.Cells(1, miscChecks.Columns.Count).End(xlToLeft).column
        
    miscChecks.Activate
    miscChecks.Range("A1").Select
    
    For i = 1 To misclastCol
                      
        ElseIf ActiveCell.Value = "MMB" Then
            Set ChktransIDcol = miscChecks.Rows("2:2").Find(what:="Reference", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
            Set MMBtransIDcol = MMB.Rows("1:1").Find(what:="Reference Number", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
            Set MMBdatacol = MMB.Rows("1:1").Find(what:=ActiveCell.Offset(1, 0).Value, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
            
            ChklastRow = miscChecks.Cells(miscChecks.Rows.Count, ChktransIDcol.column).End(xlUp).Row
            miscChecks.Cells(Chkstartrow, i).Formula = "=IFNA(INDEX(MMB!" & MMB.Columns(MMBdatacol.column).Address(Rowabsolute:=False) & ", MATCH(" & miscChecks.Cells(Chkstartrow, ChktransIDcol.column).Address(Rowabsolute:=False) & ",MMB!" & MMB.Columns(MMBtransIDcol.column).Address(Rowabsolute:=False) & ",0)),""-"")"
            
            miscChecks.Cells(Chkstartrow, i).Select
1004 Error  Selection.AutoFill Destination:=Range(Cells(Chkstartrow, i), Cells(ChklastRow, i)), Type:=xlFillDefault
            
            miscChecks.Range(miscChecks.Cells(Chkstartrow, i), Cells(ChklastRow, i)).Copy
            miscChecks.Cells(Chkstartrow, i).PasteSpecial Paste:=xlPasteValues

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). • Make sure you don't use `.Select` and make sure **every** `Range` and `Cells` object has a worksheet specified. • See if this fixes your issue. If not please update your code with the above fix, and tell the value of `Chkstartrow` and `ChklastRow` in the case of error. — Pᴇʜ, Apr 30 '21 at 09:39

score 0 · Answer 1 · answered Apr 30 '21 at 09:45

Instead of filling down the formula, you can write it directly to all cells:

So instead of …

miscChecks.Cells(Chkstartrow, i).Formula = "=IFNA(INDEX(MMB!" & MMB.Columns(MMBdatacol.column).Address(Rowabsolute:=False) & ", MATCH(" & miscChecks.Cells(Chkstartrow, ChktransIDcol.column).Address(Rowabsolute:=False) & ",MMB!" & MMB.Columns(MMBtransIDcol.column).Address(Rowabsolute:=False) & ",0)),""-"")"

do …

miscChecks.Range(miscChecks.Cells(Chkstartrow, i), miscChecks.Cells(ChklastRow, i)).Formula = "=IFNA(INDEX(MMB!" & MMB.Columns(MMBdatacol.column).Address(Rowabsolute:=False) & ", MATCH(" & miscChecks.Cells(Chkstartrow, ChktransIDcol.column).Address(Rowabsolute:=False) & ",MMB!" & MMB.Columns(MMBtransIDcol.column).Address(Rowabsolute:=False) & ",0)),""-"")"

and to turn them into values use:

miscChecks.Range(miscChecks.Cells(Chkstartrow, i), miscChecks.Cells(ChklastRow, i)).Value = miscChecks.Range(miscChecks.Cells(Chkstartrow, i), miscChecks.Cells(ChklastRow, i)).Value

AutoFill method of Range Class failed despite no recent change in code

1 Answers1