计算非空白单元格结果而不在 Excel VBA 中循环 - 例如，使用 Specialcells
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19440049/
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
Counting non blank cell results without looping in Excel VBA -- e.g., with Specialcells
提问by Bob Hopez
Here is the code I'm trying to count with in VBA, hoping to return a count return variable of "3" from 'FormulaResultCount'. Why can't I count what is visibly returned by the formulas within each cell; from the grey box (see photo below)?
这是我试图在 VBA 中计算的代码，希望从“FormulaResultCount”返回“3”的计数返回变量。为什么我不能计算每个单元格中的公式明显返回的内容；从灰色框（见下图）？
Sub countNonBlanks()
Worksheets("Sheet1").Select
Range("C:C").Select
FormulaResultCount = Selection.SpecialCells(xlCellTypeFormulas).Count
'SpecialCells options from MSFT
' xlCellTypeAllFormatConditions. Cells of any format -4172
' xlCellTypeAllValidation. Cells having validation criteria -4174
' xlCellTypeBlanks. Empty cells 4
' xlCellTypeComments. Cells containing notes -4144
' xlCellTypeConstants. Cells containing constants 2
' xlCellTypeFormulas. Cells containing formulas -4123
' xlCellTypeLastCell. The last cell in the used range 11
' xlCellTypeSameFormatConditions. Cells having the same format -4173
' xlCellTypeSameValidation. Cells having the same validation -4175
' xlCellTypeVisible. All visible cells
'
End Sub
See formula as reference:
请参阅公式作为参考：
Note: Since I will have many more cells when working dynamically, loops will likely slow the process down too much. Also, I tried using CountA without result.
注意：由于我在动态工作时会有更多的单元格，循环可能会使过程减慢太多。另外，我尝试使用 CountA 没有结果。
采纳答案by Siddharth Rout
xlCellTypeFormulas. Cells containing formulas -4123
xlCellTypeFormulas。包含公式的单元格 -4123
This would not return the cell based on their values but if they have any formula or not. As per your worksheet, you should get 5
这不会根据它们的值返回单元格，但是否有任何公式。根据您的工作表，您应该得到5
Also, PLEASE PLEASE do not use .SelectINTERESTING READ
另外，请不要使用.Select有趣的阅读
Your code can also be written as
你的代码也可以写成
FormulaResultCount = Worksheets("Sheet1").Columns(3).SpecialCells(xlCellTypeFormulas).Count
FormulaResultCount = Worksheets("Sheet1").Columns(3).SpecialCells(xlCellTypeFormulas).Count
Another Tip: When using SpecialCells, use appropriate error handling so that if there are no cells which match the SpecialCellscriteria, your code won't break. See this example.
另一个提示：使用时SpecialCells，请使用适当的错误处理，这样如果没有符合SpecialCells条件的单元格，您的代码就不会中断。请参阅此示例。
Sub Sample()
Dim ws As Worksheet
Dim Rng As Range
Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
On Error Resume Next
Set Rng = .Columns(3).SpecialCells(xlCellTypeFormulas)
If Err <> 0 Then
MsgBox "No Cells with formulas were found"
Exit Sub
End If
On Error GoTo 0
End With
FormulaResultCount = Rng.Count
Debug.Print FormulaResultCount
End Sub
FOLLOWUP From Comments
来自评论的跟进
Sub Sample()
Dim ws As Worksheet
Dim lRow As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
lRow = .Range("A" & .Rows.Count).End(xlUp).Row
Debug.Print Evaluate("=COUNTA(C1:C" & lRow & _
")-COUNTIF(C1:C" & lRow & ","""")")
End With
End Sub
回答by user3568343
Maybe this:
也许这个：
FormulaResultCount = WorksheetFunction.CountIf(Range("C:C"), "?*")
Thus counting all cells in range that start with any character?
因此计算范围内以任何字符开头的所有单元格？
回答by Derek
What you really might want is:
你真正想要的是：
FormulaResultCount = Evaluate("CountA(C:C)")
I just learnt about the evaluate command. It's awesome!
我刚刚了解了评估命令。这很棒！
And it gives you 3 :)
它给了你 3 :)
回答by Will Ediger
You can do this without VBA, using only formulas.
您可以在没有 VBA 的情况下执行此操作，仅使用公式。
=ROWS(range)*COLUMNS(range)-COUNTBLANK(range)
If you're trying to do this in VBA, you can use this:
如果您尝试在 VBA 中执行此操作，则可以使用以下命令：
Function non_blank_cell_results_count(r As Range) As Long
non_blank_cell_results_count = r.Cells.Count - WorksheetFunction.CountBlank(r)
End Function
