excel - 如何使用变量引用单元格？看似 "correct"对象范围返回错误 1004 VBA

Question

我遇到了一个问题，无论我如何尝试引用单元格，我都会收到运行时错误 1004。我尝试以多种方式引用它，但每次我尝试将公式放入单元格时，我都会卡住。

Sub LoopTest1(ByRef wbOracle As Workbook, ByRef wbReference As Workbook)

    Dim wsOracle As Worksheet
    Set wsOracle = wbOracle.Worksheets(1)
    Dim wsReference As Worksheet
    Set wsReference = wbReference.Worksheets(1)
    Dim ReferenceCell As Range 'will be used for Offset when I get to writing the looping part
    Set ReferenceCell = wsReference.Range("E16")
    Dim formulaText As String
    'Formula below searches for a match on 3 criteria and if there is a match it will write "materials supplied" granted column 9 is >= wsOracle quantity.
   formulaText = "={IF(INDEX('[" & wbReference.FullName & "]Worksheets(1)'!$A$2000:$M$2000,MATCH('[" & wbOracle.FullName & "]Worksheets(1)'!$E16&$I16&$J16,'[" & wbReference.FullName & "]Worksheets(1)'!$D:$D&'[" & wbReference.Name & "]Worksheets(1)'!$E:$E&'[" & wbReference.Name & "]Worksheets(1)'!$F:$F,0),9)>=$M16,""materials supplied"","""")}"
    wsOracle.Range("C16").Formula = formulaText 'Problem line

End Sub

我已经尝试了所有我能想到的东西，有时甚至会变得很乱。我尝试激活我没有收到错误的单元格。我尝试将其定义为变量，我尝试选择它。我还尝试清理我的公式，但仍然没有骰子。我只是非常困惑为什么我不断收到错误，因为在我看来它看起来是正确的。

Answer 1

尝试定义 formulaText如下...

formulaText = "=IF(INDEX('[" & wbReference.Name & "]" & wsReference.Name & "'!$A$2000:$M$2000,MATCH('[" & wbOracle.Name & "]" & wsOracle.Name & "'!$E16&$I16&$J16,'[" & wbReference.Name & "]" & wsReference.Name & "'!$D:$D&'[" & wbReference.Name & "]" & wsReference.Name & "'!$E:$E&'[" & wbReference.Name & "]" & wsReference.Name & "'!$F:$F,0),9)>=$M16,""materials supplied"","""")"

然后，由于您有一个数组公式，请使用 FormulaArray 而不是 Formula...

wsOracle.Range("C16").FormulaArray = formulaText

编辑

要解决字符限制，请尝试以下操作...

Sub LoopTest1(ByRef wbOracle As Workbook, ByRef wbReference As Workbook)

    Dim wsOracle As Worksheet
    Set wsOracle = wbOracle.Worksheets(1)

    Dim wsReference As Worksheet
    Set wsReference = wbReference.Worksheets(1)

    Dim ReferenceCell As Range 'will be used for Offset when I get to writing the looping part
    Set ReferenceCell = wsReference.Range("E16")

    Dim formulaPart1 As String
    Dim formulaPart2 As String
    Dim formulaPart3 As String

    formulaPart1 = "'[" & wbReference.Name & "]" & wsReference.Name & "'!$A$2000:$M$2000"
    formulaPart2 = "'[" & wbOracle.Name & "]" & wsOracle.Name & "'!$E16&$I16&$J16"
    formulaPart3 = "'[" & wbReference.Name & "]" & wsReference.Name & "'!$D:$D&'[" & wbReference.Name & "]" & wsReference.Name & "'!$E:$E&'[" & wbReference.Name & "]" & wsReference.Name & "'!$F:$F"

    With wsOracle.Range("C16")
        .FormulaArray = "=IF(INDEX(X_X_X,MATCH(Y_Y_Y,Z_Z_Z,0),9)>=$M16,""materials supplied"","""")"
        .Replace "X_X_X", formulaPart1
        .Replace "Y_Y_Y", formulaPart2
        .Replace "Z_Z_Z", formulaPart3
    End With

End Sub