vba - Count 和 Countif 公式 VBA 的运行时错误 1004

Question

将以下公式转换为代码时出现运行时错误 1004:

当我尝试考虑唯一的 Count 论坛时，它正在发挥作用。

公式:

=(COUNT(C25:C26)-COUNTIF(C25:C26,0))/(COUNT(E25:E26)-COUNTIF(E25:E26,0))

代码:

Sheets("Sheet1").Cells(29, 3).Formula = "=(COUNT(" & Sheets("Sheet1").Range(Cells(25, 3), Cells(26, 3)).Address(False, False) _
& "- COUNTIF(" & Sheets("Sheet1").Range(Cells(25, 3), Cells(26, 3)).Address(False, False) & ",0 )/" & _
"(COUNT(" & Sheets("Sheet1").Range(Cells(25, 5), Cells(26, 5)).Address(False, False) _
& "- COUNTIF(" & Sheets("Sheet1").Range(Cells(25, 5), Cells(26, 5)).Address(False, False) & ",0 ))"

Answer 1

您忘记了一些小括号/括号并留有空格。

With Sheets("Sheet1")
    .Cells(29, 3).Formula = _
    "=(COUNT(" & .Range(.Cells(25, 3), .Cells(26, 3)).Address(False, False) _
    & ")-COUNTIF(" & .Range(.Cells(25, 3), .Cells(26, 3)).Address(False, False) & ",0))/" & _
    "(COUNT(" & .Range(.Cells(25, 3), .Cells(26, 3)).Address(False, False) _
    & ")-COUNTIF(" & .Range(.Cells(25, 3), .Cells(26, 3)).Address(False, False) & ",0))"
End With

使用字符串变量进行调试并使用 MsgBox 进行提示。

Dim StrFormula As String
With Sheets("Sheet1")
    StrFormula = _
    "=(COUNT(" & .Range(.Cells(25, 3), .Cells(26, 3)).Address(False, False) _
    & ")-COUNTIF(" & .Range(.Cells(25, 3), .Cells(26, 3)).Address(False, False) & ",0))/" & _
    "(COUNT(" & .Range(.Cells(25, 3), .Cells(26, 3)).Address(False, False) _
    & ")-COUNTIF(" & .Range(.Cells(25, 3), .Cells(26, 3)).Address(False, False) & ",0))"
End With
MsgBox StrFormula