vba - 在不同的工作表中运行宏时出错

Question

我编写了一些仅适用于工作表 1 的代码。

数据输入来自工作表 2。

如果我在工作表 1 处于事件状态时运行宏，则代码可以正常工作。

但是，如果我使用工作表 3 中的按钮(作为仪表板)运行它，它将显示一条错误消息:

Run-time error '1004' Method 'Range' of object '_worksheet' failed.

Sub processsap()


 Dim SAPData As Worksheet
 Dim SAP As Worksheet
 Dim lr As Long

 Set SAPData = Worksheets("SAP Data")
 Set SAP = Worksheets("SAP")

     lr = SAPData.Range("A10000").End(xlUp).Row


 With SAP

        .UsedRange.ClearContents

        .Range("A1") = "='SAP Data'!B2"
        .Range("B1") = "='SAP Data'!C2"
        .Range("C1") = "=RIGHT(LEFT(G1,15),10)"
        .Range("D1") = "='SAP Data'!V2"
        .Range("E1") = "='SAP Data'!Q2"
        .Range("F1") = "=-99999"
        .Range("G1") = "='SAP Data'!D2"
        .Range("H1") = "='SAP Data'!O2"
        .Range("L1") = "='SAP Data'!E2"
        .Range("M1") = "='SAP Data'!F2"
        .Range("N1") = "='SAP Data'!G2"
        .Range("O1") = "='SAP Data'!M2"
        .Range("Q1") = "=IF('SAP Data'!N2=""XXXX"",""XT71"",'SAP Data'!N2)"
        .Range("T1") = "='SAP Data'!S2"
        .Range("U1") = "='SAP Data'!W2"
        .Range("AB1") = "=AJ1"
        .Range("AC1") = "=AK1"
        .Range("AF1") = "=AJ1"
        .Range("AG1") = "=AK1"
        .Range("AJ1") = "=VLOOKUP(AS1,FXrates!B:D,2,FALSE)*AK1"
        .Range("AK1") = "='SAP Data'!AD2"
        .Range("AL1") = "=AJ1"
        .Range("AS1") = "='SAP Data'!K2"
        .Range("AV1") = "=IF(D1=""IC RC - SLUITREKENING"",111983780,VLOOKUP(G1,'\\insim.biz\wpsgroups\120132\Finance\ART\2017\10\SCF\[DF ART BU Delivery SCFIN002 20171031 Macro version_CPTY V8.1 - w CPT - Copy.xlsm]Position'!$G:$AM,33,FALSE))"
        .Range("BL1") = "='SAP Data'!J2"
        .Range("CF1") = "='SAP Data'!P2"
        .Range("CJ1") = "='SAP Data'!R2"
        .Range("CW1") = "NO_GUARANTEE"
        .Range(Cells(1, 1), Cells(lr - 1, 110)).FillDown
 End With

End Sub

你知道原因吗？

谢谢

Answer 1

.Range(Cells(1, 1), Cells(lr - 1, 110)).FillDown

... 应该，

.Range(.Cells(1, 1), .Cells(lr - 1, 110)).FillDown

Recommended reading