时间戳

首页 > 解决方案 > 尽管最近没有更改代码，但 Range 类的 AutoFill 方法失败

excel - 尽管最近没有更改代码，但 Range 类的 AutoFill 方法失败

问题描述

对于以下代码，我收到错误 1004 范围类的 AutoFill 方法失败。我之前已经多次运行代码，没有任何问题。不知道是什么突然导致了这个错误。

谢谢。

    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

标签： excelvba

解决方案

您可以直接将其写入所有单元格，而不是填写公式：

所以不是……</p> 

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)),""-"")"

做……</p> 

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)),""-"")"

并将它们转化为值使用：

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

推荐阅读