在VBA公式中使用字符串变量引发对象/应用程序错误

debugcn 发表于 Dev

内特·英（Nate Eng）

尝试将活动工作表中的单元格设置为公式时，出现“应用程序定义的错误或对象定义的错误”。我认为这是由于我试图在公式中使用Sheets.Name函数，请参见下面的代码：

Public Sub getChannels()
    Dim lastRow As Long
    Dim i As Integer, counter As Integer
    Dim rng As Range, rngB As Range, rngC As Range
    Dim sht As Worksheet

    Dim test As String
    Set sht = Sheets("Summary Sheet - 30-07-2015")
    sht.Activate

    lastRow = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row
    For counter = 1 To lastRow Step 3
        If ActiveSheet.Cells(counter, 12) = "LTE 2C" Then
            ActiveSheet.Cells(counter, 16) = _
                "=INDEX('LTE 2C'!C[55],MATCH(""'"" & sht.name &""'""!RC[-14],'LTE 2C'!C[-11],0))"
            ActiveSheet.Cells(counter, 17) = _
                "=INDEX('LTE 2C'!C[53],MATCH(""'"" & sht.name &""'""!RC[-15],'LTE 2C'!C[-12],0))"
            ActiveSheet.Cells(counter, 18) = _
                "=INDEX('LTE 2C'!C[55],MATCH(""'"" & sht.name &""'""!RC[-16],'LTE 2C'!C[-13],0))"

            Range("P" & counter & ":R" & counter).Select
            Selection.Copy
            Range("P" & counter + 1 & ":P" & counter + 2).Select
            ActiveSheet.Paste
        End If

        Next

End Sub

我缺少明显的东西吗？

曼尼什基督徒

像这样更改您的公式：

ActiveSheet.Cells(counter, 16) = _
    "=INDEX('LTE 2C'!C[55],MATCH(" & "'" & sht.name & "'" & "!RC[-14],'LTE 2C'!C[-11],0))"
ActiveSheet.Cells(counter, 17) = _
    "=INDEX('LTE 2C'!C[53],MATCH(" & "'" & sht.name & "'" & "!RC[-15],'LTE 2C'!C[-12],0))"
ActiveSheet.Cells(counter, 18) = _
    "=INDEX('LTE 2C'!C[55],MATCH(" & "'" & sht.name & "'" & "!RC[-16],'LTE 2C'!C[-13],0))"

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。