错误1004由=？

Question

我在stackoverflow上检查了很多不同的解决方案，但它们都不适用于我。

当我插入一个像"1“这样的值时，一切正常，但是当我尝试插入我的公式时，我总是得到一个运行时错误。有谁能解释一下吗？将非常感谢您的帮助！

Sub test()


Dim cell As Excel.Range
Dim wsWithSheetNames As Excel.Worksheet
Dim wbToAddSheetsTo As Excel.Workbook

Set wsWithSheetNames = ActiveSheet
Set wbToAddSheetsTo = ActiveWorkbook


For wsname = 1 To 254
For Each cell In wsWithSheetNames.Range("A" & wsname)
With wbToAddSheetsTo
    .Sheets.Add After:=.Sheets(.Sheets.Count)
    On Error Resume Next
    ActiveSheet.Name = cell.Value
    wsname2 = cell.Value
    If Err.Number = 1004 Then
      Debug.Print cell.Value & " already used as a sheet name"
    End If
    On Error GoTo 0
End With
Next cell

Worksheets("Sheet1").Range("B1", "B1735").Copy 
Worksheets(wsname2).Range("A2")
(HERE IS THE PROBLEM!)Worksheets(wsname2).Range("B2:AGR1736").FormulaLocal = 
"=IFERROR(VLOOKUP($A2,OFFSET([final.xlsb]" & CHr(34) & wsname2 & Chr(34) & "!$A$1:$D$2000,0,
(COLUMN(A2)*4-4)),4,0),"")"


Next wsname


End Sub

编辑:将行更改为：

Worksheets(wsname2).Range("B2:AGR1736").FormulaLocal = 
"=IFERROR(VLOOKUP($A2,OFFSET([final.xlsb]" & wsname2 & "!$A$1:$D$2000,0,
(COLUMN(A2)*4-4)),4,0)," & Chr(34) & Chr(34) & ")"

如果我不写"=“代码就行了，为什么它不能写"=”呢？

Answer 1

我认为你需要声明wsname和wsname2，所以完整的代码应该是这样的：

Sub test()

Dim cell As Excel.Range
Dim wsWithSheetNames As Excel.Worksheet
Dim wbToAddSheetsTo As Excel.Workbook
Dim wsname As Integer
Dim wsname2 As String

Set wsWithSheetNames = ActiveSheet
Set wbToAddSheetsTo = ActiveWorkbook


For wsname = 1 To 254
For Each cell In wsWithSheetNames.Range("A" & wsname)
With wbToAddSheetsTo
    .Sheets.Add After:=.Sheets(.Sheets.Count)
    On Error Resume Next
    ActiveSheet.Name = cell.Value
    wsname2 = cell.Value
    If Err.Number = 1004 Then
      Debug.Print cell.Value & " already used as a sheet name"
    End If
    On Error GoTo 0
End With
Next cell

Worksheets("Sheet1").Range("B1", "B1735").Copy Worksheets(wsname2).Range("A2")
'Worksheets(wsname2).Range("B2:AGR1736").FormulaLocal = 
'"=IFERROR(VLOOKUP($A2,OFFSET([final.xlsb]" & CHr(34) & wsname2 & Chr(34) & "!$A$1:$D$2000,0,
'(COLUMN(A2)*4-4)),4,0),"")"

Worksheets(wsname2).Range("B2:AGR1736") = "=IFERROR(VLOOKUP($A2,OFFSET([final.xlsb]" & wsname2 & "!$A$1:$D$2000,0," & "(COLUMN(A2)*4-4)),4,0)," & Chr(34) & Chr(34) & ")"


Next wsname


End Sub

希望这能有所帮助。