VBA 计算包含指定值的列中的单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8592915/
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
VBA Count cells in column containing specified value
提问by BradStevenson
I need to write a macro that searches a specified column and counts all the cells that contain a specified string, such as "19/12/11" or "Green"then associate this number with a variable,
我需要编写一个宏来搜索指定的列并计算包含指定字符串的所有单元格，例如"19/12/11" or "Green"然后将此数字与变量关联，
Does anyone have any ideas?
有没有人有任何想法？
回答by JMax
Do you mean you want to use a formula in VBA? Something like:
你的意思是你想在 VBA 中使用公式吗？就像是：
Dim iVal As Integer
iVal = Application.WorksheetFunction.COUNTIF(Range("A1:A10"),"Green")
should work.
应该管用。
回答by Tezzums
This isn't exactlywhat you are looking for but here is how I've approached this problem in the past;
这不正是你所期待的，但这里是我是如何接近过去这个问题;
You can enter a formula like;
您可以输入一个公式，如；
=COUNTIF(A1:A10,"Green")
...into a cell. This will count the Number of cells between A1 and A10 that contain the text "Green". You can then select this cell value in a VBA Macro and assign it to a variable as normal.
...进入一个单元格。这将计算 A1 和 A10 之间包含文本“绿色”的单元格数。然后，您可以在 VBA 宏中选择此单元格值，并照常将其分配给变量。
回答by Alex K.
one way;
单程;
var = count("find me", Range("A1:A100"))
function count(find as string, lookin as range) As Long
dim cell As Range
for each cell in lookin
if (cell.Value = find) then count = count + 1 '//case sens
next
end function
回答by Duarte Botelho
Not what you asked but may be useful nevertheless.
不是你问的，但可能有用。
Of course you can do the same thing with matrix formulas. Just read the result of the cell that contains:
当然，你可以用矩阵公式做同样的事情。只需读取包含以下单元格的结果：
Cell A1="Text to search"Cells A2:C20=Range to search for
Cell A1="Text to search"Cells A2:C20=Range to search for
=COUNT(SEARCH(A1;A2:C20;1))
Remember that entering matrix formulas needs CTRL+SHIFT+ENTER, not just ENTER. After, it should look like :
请记住，输入矩阵公式需要 CTRL+SHIFT+ENTER，而不仅仅是 ENTER。之后，它应该是这样的：
{=COUNT(SEARCH(A1;A2:C20;1))}
{=COUNT(SEARCH(A1;A2:C20;1))}
