vba - 错误 400 消息 - 不知道如何解决公式

Question

嗨，我通过浏览本网站上的帖子将这个 VBA 放在一起，这使我能够在表格中运行所有值的公式。但是，每次我运行它时，我都会收到一条错误 400 消息。我在哪里做错了，我将如何使这个 vba 工作以使用由另一张纸上的列确定的长度。

Sub FillWorksheet1()

Application.ScreenUpdating = False

lastRow = Range("B" & Rows.Count).End(xlUp).Row

Range("AF2").Formula = "=firstPart($G2)"
Range("AF2").AutoFill Destination:=Range("AF2:AF" & lastRow)

Range("AG2").Formula = "=VLOOKUP($AF2,'Naming Lookup'!$A$2:$G$10815,2,FALSE)"
Range("AG2").AutoFill Destination:=Range("AG2:AG" & lastRow)

Range(AH2).Formula = "=VLOOKUP($AF2,'Naming Lookup'!$A$2:$G$10815,3,FALSE)"
Range("AH2").AutoFill Destination:=Range("AH2:AH" & lastRow)

Range(AI2).Formula = "=VLOOKUP($AF2,'Naming Lookup'!$A$2:$G$10815,4,FALSE)"
Range("AI2").AutoFill Destination:=Range("AI2:AI" & lastRow)

Range(AJ2).Formula = "=VLOOKUP($AF2,'Naming Lookup'!$A$2:$G$10815,5,FALSE)"
Range("AJ2").AutoFill Destination:=Range("AJ2:AJ" & lastRow)

Range(AK2).Formula = "=VLOOKUP($AF2,'Naming Lookup'!$A$2:$G$10815,6,FALSE)"
Range("AK2").AutoFill Destination:=Range("AK2:AK" & lastRow)

Range(AL2).Formula = "=VLOOKUP($AF2,'Naming Lookup'!$A$2:$G$10815,7,FALSE)"
Range("AL2").AutoFill Destination:=Range("AL2:AL" & lastRow)
ActiveSheet.AutoFilterMode = False

Application.ScreenUpdating = True

End Sub

任何帮助将非常感激。

firstPart 公式由 VBA 创建，用于删除数字后面的文本，以便收集产品代码，而不仅仅是客户特定的代码。

Answer 1

我会这样做:

Sub FillWorksheet1()
    Dim lastRow               As Long
    Dim lCalc                 As XlCalculation

    With Application
        .ScreenUpdating = False
        lCalc = .Calculation
        .Calculation = xlCalculationManual
    End With

    lastRow = Sheets("some other sheet").Range("B" & Rows.Count).End(xlUp).Row

    Range("AF2").Formula = "=firstPart($G2)"
    Range("AG2").Formula = "=MATCH($AF2,'Naming Lookup'!$A$2:$A$10815,0)"
    Range("AH2:AM2").Formula = "=INDEX('Naming Lookup'!B$2:B$10815,$AG2)"
    Range("AF2:AM2").AutoFill Destination:=Range("AF2:AM" & lastRow)
    ActiveSheet.AutoFilterMode = False

    With Application
        .Calculation = lCalc
        .ScreenUpdating = True
    End With

End Sub