vba WorksheetFunction.CountA 没有返回正确的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14897423/
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
WorksheetFunction.CountA not returning correct value
提问by Amit Kumar Jha
What I am trying to do is iterate through a range containing worksheet names, and if the cell is not empty then add the result of CountAfunction to the count variable.
我想要做的是遍历包含工作表名称的范围，如果单元格不为空，则将CountA函数的结果添加到计数变量中。
So the count variable should be equal to number of non-blank cells in range B9:B28on the worksheets I'm iterating through, but strangely the value is equal to the number of non empty cells in the range I'm going through (sheet1!d5:d24).
因此，计数变量应等于B9:B28我正在迭代的工作表上范围内非空单元格的数量，但奇怪的是，该值等于我正在通过的范围内的非空单元格数量 ( sheet1!d5:d24)。
What am I doing wrong? Here's the code I am using:
我究竟做错了什么？这是我正在使用的代码：
For Each c In Worksheets("Sheet1").Range("d5:d24").Cells
If Not IsEmpty(c) Then
count = count + WorksheetFunction.CountA(c & "!b9:b28")
End If
Next
I tried an alternative method to loop through second range and if the cells are not empty, then increment the variable by 1 but that's giving a Run time error 13 type mismatcherror. This is what I am doing now:
我尝试了另一种方法来循环遍历第二个范围，如果单元格不为空，则将变量增加 1，但这会导致运行时错误 13 类型不匹配错误。这就是我现在正在做的：
For Each c In Worksheets("Sheet1").Range("d5:d24")
If Not IsEmpty(c) Then
For Each c2 In Worksheets(c).Range("b9:b28")
If Not IsEmpty(c2) Then
'count = count + WorksheetFunction.CountA(c & "!b9:b28")
count = count + 1
End If
Next
End If
Next
Please help me out. Thanks a lot in advance to all those who take out time to reply.
请帮帮我。非常感谢所有抽出时间回复的人。
回答by Amit Kumar Jha
Based on @Peter Albert and @Peter L. 's comments, finally got it working. The correct code is:
根据@Peter Albert 和@Peter L. 的评论，终于让它工作了。正确的代码是：
For Each c In Worksheets("Sheet1").Range("d5:d24").Cells
If Not IsEmpty(c.Value) Then
count = count + WorksheetFunction.CountA(c.Value & "!b9:b28")
End If
Next
Thanks a lot guys :)
非常感谢各位:)
回答by David Zemens
Try this:
尝试这个：
Sub CountColBForColD()
Dim c As Range
Dim r As Long 'row counter
Dim rngB As Range
Dim rngD As Range
Dim lookSheet As Worksheet
Set rngD = Sheets("Sheet1").Range("D5:D24")
Set rngB = Range("B9:B28")
r = 1
For Each c In rngD
If Not IsEmpty(c) Then
On Error GoTo InvalidSheetName
Set lookSheet = Sheets(rngB(r).Value)
On Error GoTo 0
Count = Count + WorksheetFunction.CountA( _
lookSheet.Range(rngB.Address))
c.Offset(0, 1).Value = Count
r = r + 1
End If
NxtC:
Next
Exit Sub
InvalidSheetName:
Err.Clear
MsgBox "Sheet named in " & rngB(r).Address & " does not exist.", vbInformation
Resume NxtC
End Sub
