vba 如何计算excel中非零的数量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11196888/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I count the number of non-zeros in excel?
提问by monkthemighty
I am trying to make a macro that will go through a whole workbook and count the number of days a employee worked. The sheets have the work broken out in days so all T have to find is the days that are not zero. I have tried to use COUNTIF(A11:A12,">0")and I get the error Expected : list separator or ). I am using a For Eachloop to work through the sheets. I would like to put all the information on a new sheet at the end of the workbook with the name of the employee and the days worked. I am very new to visual basic but am quite good with c#.
我正在尝试制作一个宏，它将遍历整个工作簿并计算员工工作的天数。工作表以天为单位分解工作，因此 T 必须找到的只是不为零的天数。我尝试使用COUNTIF(A11:A12,">0")，但出现错误Expected : list separator or )。我正在使用For Each循环来处理工作表。我想将所有信息放在工作簿末尾的新工作表上，其中包含员工姓名和工作天数。我对visual basic非常陌生，但对c#非常熟悉。
I now have gotten this far
我现在已经到了这一步
Option Explicit
Sub WorksheetLoop2()
' Declare Current as a worksheet object variable.
Dim Current As Worksheet
Dim LastColumn As Integer
If WorksheetFunction.CountA(Cells) > 0 Then
' Search for any entry, by searching backwards by Columns.
LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
End If
' Loop through all of the worksheets in the active workbook.
For Each Current In Worksheets
Current.Range("A27") = Application.WorksheetFunction.CountIf(Current.Range(Cells(11, LastColumn), Cells(16, LastColumn)), ">0")
Current.Range("A28") = Application.WorksheetFunction.CountIf(Current.Range("Al17:Al22"), ">0")
Next
End Sub
When I run this I get an error saying method range of object'_worksheet' failed. I also haven't been able to find a way to get the information all on the summary sheet.
当我运行它时，我收到一条错误消息method range of object'_worksheet' failed。我也无法找到一种方法来获取汇总表上的所有信息。
回答by Scott Holtzman
VBA Solution, in light of your last comment above.
VBA 解决方案，根据您上面的最后一条评论。
Good VBA programming practice entails always using Option Explicit with your code, that way you know when you don't have variables declared correctly, or, sometimes, if code is bad! In this case you would have picked up that just writing A27 does not mean you are returning the value to cell A27, but rather just setting the value you get to variable A27. Or maybe you wouldn't know that exactly, but you would find out where your problem is real quick!
良好的 VBA 编程实践需要始终在您的代码中使用 Option Explicit，这样您就可以知道何时没有正确声明变量，或者有时，如果代码很糟糕！在这种情况下，您会发现仅写入 A27 并不意味着您将值返回到单元格 A27，而只是将您获得的值设置为变量 A27。或者也许您不会确切地知道这一点，但是您会很快发现问题所在！
This code should fix it for you:
此代码应该为您修复它：
Option Explicit
Sub WorksheetLoop2()
'Declare Current as a worksheet object variable.
Dim Current As Worksheet
' Loop through all of the worksheets in the active workbook.
For Each Current In Worksheets
Current.Range("A27") = Application.WorksheetFunction.CountIf(Current.Range("A11:A12"), ">0")
Next
End Sub
In case it helps, Non-VBA solution:
如果有帮助，非 VBA 解决方案：
Assuming you have a Summary sheet and each employee on a separate sheet, with days in column A and hours worked in column B, enter formula in formula bar in B1 of Summary and run down the list of names in column A.
假设您有一个汇总表，每个员工都在一个单独的表上，A 列中的天数和 B 列中的工作小时数，在汇总的 B1 中的公式栏中输入公式，然后运行 A 列中的姓名列表。
