vba 计算并给出结果的宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15930750/
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
macro to count and give result
提问by user2254486
Can anyone help me. I want to count how many of the numbers are > 45 and put the result 3 rows below the last data cell. Lets give it a name - call it result. Then to the left of result I would like to put the words Number > 45. The amount of data rows will change, so when I run the macro on column D it will find the last data point and do the calculation. Some of the rows will be empty. Thanks for the help
谁能帮我。我想计算有多少个数字 > 45 并将结果放在最后一个数据单元格下方 3 行。让我们给它一个名字——称之为结果。然后在结果的左边，我想把 Number > 45 这个词放上。数据行的数量会改变，所以当我在 D 列上运行宏时，它会找到最后一个数据点并进行计算。一些行将是空的。谢谢您的帮助
Its should like that this
它应该是这样的
50
20
100
120
45
30
30
Return >45= 4
回报 >45=4
Sub enter()
Dim result As Integer
Dim firstrow As Integer
Dim lastwow As Integer
Firstrow = d2
Result = ‘ Value of count
Worksheets("sheet1").Range("c?").Value = "Total>45"
Range("d100000").End(xlUp).Select
End Sub
回答by Siddharth Rout
Try this
尝试这个
Sub Sample()
Dim result As Long, firstrow As Long, lastrow As Long
Dim ws As Worksheet
Dim rng As Range
'~~> Set this to the relevant worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
'~~> Find Lastrow in Col D
lastrow = .Range("D" & .Rows.Count).End(xlUp).Row
'~~> Set First row
firstrow = 1
'~~> Set your range
Set rng = .Range("D" & firstrow & ":D" & lastrow)
'~~> Put relevant values
.Range("C" & lastrow + 3).Value = "Total>45"
.Range("D" & lastrow + 3).Value = _
Application.WorksheetFunction.CountIf(rng, ">45")
End With
End Sub
Screenshot
截屏
回答by Dick Kusleika
Here's one that will let you pass in any number, not just 45
这是一个可以让你通过任何数字的，而不仅仅是 45
Sub MakeCount(lGreaterThan As Long)
Dim lLast As Long
With Sheet1
lLast = .Cells(.Rows.Count, 4).End(xlUp).Row
.Cells(lLast + 3, 4).FormulaR1C1 = "=COUNTIF(R[-" & lLast + 1 & "]C:R[-3]C,"">""&RC[-1])"
.Cells(lLast + 3, 3).Value = lGreaterThan
.Cells(lLast + 3, 3).NumberFormat = """Number>""#"
End With
End Sub
回答by Our Man in Bananas
can't you use a worksheet formula like
你不能使用像这样的工作表公式吗
=COUNTIF(A2:A7,">45")
=COUNTIF(A2:A7,">45")
alternatively, in VBA as Mr Siddharth Routsuggests in his answer
或者，在 VBA 中，正如Siddharth Rout先生在他的回答中所建议的那样
回答by SeanC
is vba required?
需要vba吗？
if not, the function =COUNTIF(C:C,">45")will give you the answer you want.
如果没有，该功能=COUNTIF(C:C,">45")会给你你想要的答案。
