vba Excel Application.WorksheetFunction.CountIf 使用相对引用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10494041/
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
Excel Application.WorksheetFunction.CountIf using relative references
提问by Swiftslide
I'll try keep this as simple and nonspecific as I can. I have three columns, a, b, c. Col C has one-hundred random numbers in cells C1-C100. Col A has five random numbers in cells C1:C5.
我会尽量保持简单和非特定。我有三列，a，b，c。Col C 在单元格 C1-C100 中有一百个随机数。Col A 在单元格 C1:C5 中有五个随机数。
For each cell B1:B5, I want to enter how many times the number in col a appears in col c. If you were writing the formula directly into cell B1, it would be thus:
对于每个单元格 B1:B5，我想输入 col a 中的数字出现在 col c 中的次数。如果您将公式直接写入单元格 B1，则为：
=COUNTIF($C:$C, $A1)
=COUNTIF($C:$C, $A1)
You would then drag down to fill cells B2:B5
然后向下拖动以填充单元格 B2:B5
I want to do this using VBA's Application.WorksheetFunction.CountIf function (my actual project is much more complex than this, and cells need to be filled in automatically).
我想使用 VBA 的 Application.WorksheetFunction.CountIf 函数来做到这一点（我的实际项目比这复杂得多，需要自动填充单元格）。
Dim colb As Range
Set colb = Range("$B1:$B5")
Dim colc As Range
Set colc = Range("$C:$C")
Dim cola As Range
Set cola = Range("$A:$A")
For Each bcell In colb
bcell.Formula = Application.WorksheetFunction.CountIf(colc, bcell.Offset(0, -1))
Next bcell
This works correctly. But I'm wondering if it can be done without a For-Each loop, and without specifying how many cells in B are to be filled in, (recognising when A reaches its last value, and stops filling B in accordingly, a la
这工作正常。但我想知道是否可以在没有 For-Each 循环的情况下完成它，并且没有指定要填充 B 中的多少个单元格，（识别 A 何时达到其最后一个值，并相应地停止填充 B，a la
Dim colb As Range
Set colb = Range("$B:$B")
In my mind the crucial line would be something like:
在我看来，关键线是这样的：
colb.Formula = Application.WorksheetFunction.CountIf(colc, cola)
But this does not work. Is anything I'm asking possible? I imagine letting Excel fill in the whole range at once would be faster than looping.
但这不起作用。我问的有什么可能吗？我想让 Excel 一次填充整个范围会比循环更快。
回答by Charles Williams
You could use evaluate:
您可以使用评估：
Sub testeval()
Dim vArr As Variant
Dim nARows As Long
Dim nCRows As Long
Dim oSht1 As Worksheet
Dim strSheet As String
Set oSht1 = ActiveSheet
strSheet = oSht1.Name & "!"
nARows = oSht1.Range("A1").Offset(oSht1.Rows.Count - 1).End(xlUp).Row
nCRows = oSht1.Range("C1").Offset(oSht1.Rows.Count - 1).End(xlUp).Row
vArr = oSht1.Evaluate("=INDEX(COUNTIF(" & strSheet & "$C1:$C" & CStr(nCRows) & "," & strSheet & "$A1:$A" & CStr(nARows) & "),0,1)")
oSht1.Range("$B1").Resize(UBound(vArr), 1) = vArr
End Sub
回答by Siddharth Rout
You don't need the loop nor do you need to use the worksheetfunction :) This will do that what you want.
您不需要循环，也不需要使用工作表函数 :) 这将完成您想要的操作。
Range("$B1:$B5").Formula="=COUNTIF($C:$C, $A1)"
EXAMPLE
例子
Option Explicit
Sub Sample()
Dim ws As Worksheet
Dim colb As Range
Set ws = Sheets("Sheet1")
Set colb = ws.Range("$B1:$B5")
colb.Formula = "=COUNTIF($C:$C, $A1)"
End Sub
