Select.AutofillDestination:=范圍不自動填充

我已經使用 VBA 大約兩年了，但我遇到了以前從未遇到過的問題。我正在嘗試使用基于 L 列中填充的單元格數量的公式自動填充 A、B、D、E 和 G 列。在下面顯示的示例中，填充了單元格 L2-L5。使用我的自動填充公式，這應該意味著單元格 A2-A5 填充，單元格 B2-B5 填充等。但它們沒有。只有 G 列正確填充，我很難理解為什么會這樣。有什么絕妙的主意嗎？

這是我的代碼：

Application.DisplayAlerts = False

Sheets("QBTimecard").Select
    Range("DP2").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-119]&""_""&RC[-107]&""_""&IF(RC[-2]<>""Client Transportation"",""Z"",""XY"")"
    Range("DQ2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-13]<=RC[-8],""Pass"",""MMV"")"
    On Error Resume Next
    Selection.Autofill Destination:=Range("dp2:ds" & Cells(Rows.Count, "A").End(xlUp).Row)

Sheets("QBTimecard").Select
    Columns("DP:DP").Select
    Selection.Copy
Sheets("PunchEntryImport").Select
    Columns("L:L").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("L1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Blank"
    Range("L2").Select

 Sheets("PunchEntryImport").Select
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX(QBTimecard!C,MATCH(PunchEntryImport!RC[11],QBTimecard!C[119],0))"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(QBTimecard!C[118],PunchEntryImport!RC[10],QBTimecard!C[13])/COUNTIF(QBTimecard!C[118],PunchEntryImport!RC[10])"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX(Lookup!C[-2],MATCH(INDEX(QBTimecard!C[9],MATCH(PunchEntryImport!RC[8],QBTimecard!C[116],0)),Lookup!C[-3],0))&""/1/""&INDEX(QBTimecard!C[10],MATCH(PunchEntryImport!RC[8],QBTimecard!C[116],0))&"" ""&TEXT(INDEX(QBTimecard!C[12],MATCH(PunchEntryImport!RC[8],QBTimecard!C[116],0)),""HHMM"")"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX(Lookup!C[-3],MATCH(INDEX(QBTimecard!C[8],MATCH(PunchEntryImport!RC[7],QBTimecard!C[115],0)),Lookup!C[-4],0))&""/1/""&INDEX(QBTimecard!C[9],MATCH(PunchEntryImport!RC[7],QBTimecard!C[115],0))&"" ""&TEXT(INDEX(QBTimecard!C[12],MATCH(PunchEntryImport!RC[7],QBTimecard!C[115],0)),""HHMM"")"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX(QBTimecard!C[103],MATCH(PunchEntryImport!RC[5],QBTimecard!C[113],0))"
   On Error Resume Next

'This is the portion that is not autofilling   
   Selection.Autofill Destination:=Range("a2:a" & Cells(Rows.Count, "L").End(xlUp).Row)
   Selection.Autofill Destination:=Range("b2:b" & Cells(Rows.Count, "L").End(xlUp).Row)
   Selection.Autofill Destination:=Range("d2:d" & Cells(Rows.Count, "L").End(xlUp).Row)
   Selection.Autofill Destination:=Range("e2:e" & Cells(Rows.Count, "L").End(xlUp).Row)
   Selection.Autofill Destination:=Range("g2:g" & Cells(Rows.Count, "L").End(xlUp).Row)

uj5u.com熱心網友回復：

無需重寫整個內容，這是一種更容易處理的方法，無需選擇/激活/自動填充：

Dim wsQBTC As Worksheet, wsPE As Worksheet, lr As Long

Set wsQBTC = Worksheets("QBTimecard")
Set wsPEI = Worksheets("PunchEntryImport")

lr = wsQBTC.Cells(Rows.Count, "A").End(xlUp).Row
With wsQBTC
    .Range("DP2:DP" & lr).FormulaR1C1 = "=RC[-119]&""_""&RC[-107]&""_""&IF(RC[-2]<>""Client Transportation"",""Z"",""XY"")"
    .Range("DQ2:DQ" & lr).FormulaR1C1 = "=IF(RC[-13]<=RC[-8],""Pass"",""MMV"")"
End With

uj5u.com熱心網友回復：

來自Range.AutoFill的幫助

目標必須包括源范圍。

因為您正在使用Selection.AutoFill并且 G2 是活動單元格，所以只有當 G2 是自動填充范圍的一部分時，自動填充才會起作用。

要解決此問題，請替換Selection.AutoFill為Range("A2").AutoFill，Range("B2").AutoFill依此類推

標籤：擅长 vba

上一篇：使用VBA禁用關閉(X)并需要一個按鈕來關閉和保存

下一篇：如何在用戶定義的VBA函式中從另一個作業表中提取資料