vba CountIf 可变范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22917883/
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
CountIf With Variable Range
提问by user646464
I am trying to use write a macro using COUNTIF to find the number of times a number appears in a range. It is a list of ratings, from 1 to 7. The issue is that the number of rows will vary each time, as well as the column where the ratings will be.
我正在尝试使用 COUNTIF 编写宏来查找数字在范围内出现的次数。它是一个评分列表，从 1 到 7。问题是每次的行数以及评分所在的列都会有所不同。
I actually did write this successfully before, but I lost all my code when my hard drive crashed! So I know this can be done, but I don't remember how I did it. Here is my code with comments:
我之前确实写过这个成功，但是当我的硬盘驱动器崩溃时我丢失了所有代码！所以我知道这可以做到，但我不记得我是怎么做到的。这是我的带有注释的代码：
'find the cell called "Rating". In this example, it will be in $E
Cells.Find(What:="Rating", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=True).Activate
'This will be $E
Top = ActiveCell.Address
'This will be 5, for column E
CurrentColumn = ActiveCell.Column
'go to the bottom cell of the range
Cells(50000, CurrentColumn).End(xlUp).Select
'This will be $E
Bottom = ActiveCell.Address
'Combine the top and bottom to make the range, which will be $E:$E
RangeToSelect = Top & ":" & Bottom
'Under the range, go down 4 cells and do a COUNTIF for the numbers 7 to 1
ActiveCell.Offset(4, 0).Range("A1").Select
For xx = 7 To 1 Step -1
ActiveCell.FormulaR1C1 = "=COUNTIF(" & RangeToSelect & "," & xx & ")"
ActiveCell.Offset(1, 0).Range("A1").Select
Next xx
The code where it throws the error is:
它抛出错误的代码是：
ActiveCell.FormulaR1C1 = "=COUNTIF(" & RangeToSelect & "," & xx & ")"
It should end up being =COUNTIF($E$13:$E$37,7) for the first cell, then finding 6, 5,...1. Any help and/or suggestions would be greatly appreciated!!!
对于第一个单元格，它最终应该是 =COUNTIF($E$13:$E$37,7)，然后找到 6, 5,...1。任何帮助和/或建议将不胜感激！！！
回答by engineersmnky
You could do this
你可以这样做
Dim RangeToSelect AS Range
Set RangeToSelect = ActiveWorksheet.Range(Top,Bottom)
For xx = 7 To 1 Step -1
ActiveCell.Value = Application.WorksheetFunction.CountIf(RangeToSelect,xx)
ActiveCell.Offset(1, 0).Select
Next xx
Unless you really need the formula in the cell.
除非你真的需要单元格中的公式。
回答by Dan Wagner
The following will write out the results of the countif:
下面将写出countif的结果：
Option Explicit
Sub Stack()
Dim MySheet As Worksheet
Dim FoundRng As Range, RangeToSelect As Range
Dim LastRow As Long, xx As Long
'assign our sheet to avoid confusion
Set MySheet = ThisWorkbook.ActiveSheet
'locate the "Rating" cell
Set FoundRng = MySheet.Cells.Find(What:="Rating", LookAt:=xlWhole, MatchCase:=False)
If FoundRng Is Nothing Then
MsgBox ("No matching cell found, exiting sub!")
Exit Sub
End If
'determine the boundaries of the target range for populating the formula
LastRow = MySheet.Cells(50000, FoundRng.Column).End(xlUp).Row
'assign the target range
Set RangeToSelect = Range(MySheet.Cells(FoundRng.Row, FoundRng.Column), MySheet.Cells(LastRow, FoundRng.Column))
'write out the countif results
MySheet.Cells(LastRow + 4, FoundRng.Column).Select
For xx = 7 To 1 Step -1
ActiveCell.Value = Application.WorksheetFunction.CountIf(RangeToSelect, xx)
ActiveCell.Offset(1, 0).Select
Next xx
End Sub
