英文:
VBA CountIf syntax failure?
问题
使用以下简单的VBA代码，我尝试显示范围内单元格值大于六的总数。我仍然无法弄清楚为什么CountIf函数无法按预期工作。
请帮助我修复在这里应用CountIf函数时的缺陷。
Sub countcells() '
Dim rng As Range
Set rng = Range("A1:E2")
Range("G1") = CountIf(rng, "> 6")
End Sub
以下错误消息由Excel模块引发：
https://www.screencast.com/t/Ip8jyuUO11
谢谢
在阅读了“整个网络”之后，我觉得在这段代码中语法已经正确应用。显然情况并非如此。
英文:
With the below simple VBA code I attempt to display the total number of cells in a range where the cell value is greater than six. I still can't figure out why CountIf function won't work out as expected.
Please assist me how to repair the imperfection when applying CountIf function here.
Sub countcells() '
Dim rng As Range
Set rng = Range("A1:E2")
Range("G1") = CountIf(rng, "> 6")
End Sub
The following error message is thrown by the Excel module:
https://www.screencast.com/t/Ip8jyuUO11
Thanks
After reading through the "entire web" it seems to me that the syntax is properly applied in this code. Obviously that is not the case.
答案1
得分: 0
你需要在代码中指定WorksheetFunction对象来访问CountIf。这将在子例程中计算结果。如果A1:E2中的值被修改，你将需要重新运行VBA代码。
如果你希望在工作表中使用COUNTIF并实现自动更新，也可以这样做。
Sub CountCells()
Dim rng As Range
Set rng = Range("A1:E2")
Range("G1").Value = WorksheetFunction.CountIf(rng, "> 6")
Range("G2").Formula = "=COUNTIF(" & rng.Address(False, False) & ",""> 6"")"
End Sub
英文:
You have to specify the WorksheetFunction object to access CountIf in code. This will calculate the result within the subroutine. If values in A1:E2 are modified you will have to re-run the VBA code.
If you want COUNTIF in the sheet so it updates automatically then you can do that as well.
Sub CountCells()
Dim rng As Range
Set rng = Range("A1:E2")
Range("G1").Value = WorksheetFunction.CountIf(rng, "> 6")
Range("G2").Formula = "=COUNTIF(" & rng.Address(False, False) & ",""> 6"")"
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论