Excel VBA：将公式插入单元格会产生运行时错误'1004'

Question

我查看了有关使用 VBA 以编程方式将公式插入 Excel 单元格时的错误的其他帖子。但是这些技巧似乎都无助于我尝试插入的特定公式。.formula操作语法似乎没问题，因为如果我使用像"=3+3"这样的简单公式，代码执行正常。我想插入的公式似乎也可以，因为当我在即时窗口中查看它然后手动粘贴到 Excel 单元格中时，它可以正常工作......任何想法！代码如下：

Public Sub Populate_Formulae()
Dim oWrkBk As Workbook
Dim oWrkSht As Worksheet
Dim strLineNumber As String
Dim intCounter As Integer
Dim rngColB As Range
Dim rngColA As Range
Dim rngColD As Range
Dim strFormula As String
Dim intCellCounterLo As Integer
Dim intCellCounterHi As Integer
Set oWrkBk = Excel.ActiveWorkbook
Set oWrkSht = oWrkBk.Sheets("WerkBonApp")
intCellCounterLo = 10 'first source row to check in
intCellCounterHi = 29 'last source row to check in
For intCounter = 17 To 362 'start processing the target sheet at row 17 "LIJN C"
'Determine which row we are parsing by checking column B
Set rngColB = oWrkSht.Cells(intCounter, 2)
If InStr(1, rngColB.Text, "LIJN") <> 0 Then
strLineNumber = Right$(rngColB.Text, 1)
Else
'Put the formulae in the cells in column A
'Set Column A formula
Set rngColA = oWrkSht.Cells(intCounter, 1) 'get the current cell to populate the formula
' create the formula string
strFormula = "=VLOOKUP(MAX('Lijn " & strLineNumber & "'!I" & intCellCounterLo & ":'Lijn " & strLineNumber & "'!I" & intCellCounterHi & ");'Lijn " _
& strLineNumber & "'!I" & intCellCounterLo & ":'Lijn " & strLineNumber & "'!J" & intCellCounterHi & ";2;FALSE)"
'strFormula now equates to:
' =VLOOKUP(MAX('Lijn C'!I10:'Lijn C'!I29);'Lijn C'!I10:'Lijn C'!J29;2;FALSE)
' this works when manually entered into the Excel cell
' but gives error when executed in VBA code:
' Run-time error '1004':
' Application-defined or object-defined error
'put the formula into the current cell
rngColA.Formula = strFormula 'get error message. This function works if strFormula= "=3+3" , for example.
End If
Next intCounter
End Sub

非常欢迎任何和所有的建议！

保罗

Answer 1

尝试使用 VBA 将复杂公式插入 Excel 单元格时，需要使用.FormulaLocal属性。