带有Count和Countif公式VBA的运行时错误1004

user1204868 发表于 Dev

用户名

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

当我尝试考虑唯一的Count Forumlas时，它正在工作。

公式：

=(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 ))"

R3uK

您忘记了一些小技巧/括号，让了空格。

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

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

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