在最后一行的 VBA 中使用 COUNTIF
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18360798/
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
Using COUNTIF in VBA with last row
提问by AAli
I'm quite new to excel and have been trying to get this Countif formula to work for a while now. I want it to count from the 12th row in column AN p till the last used row. I am very close now but when I run the macro it gives me a REF error.
我对 excel 很陌生，并且一直试图让这个 Countif 公式工作一段时间。我希望它从 AN p 列的第 12 行开始计数，直到最后使用的行。我现在非常接近，但是当我运行宏时，它给了我一个 REF 错误。
Sub Date1()
'
' Enter Date
'
Range("B15") = InputBox("Enter Date")
Dim LR As Long
LR = Sheets("Design Risk Scoring Sheet").Range("AN" & Rows.count).End(xlUp).Row
Range("B16").FormulaR1C1 = _
"=COUNTIF('Design Risk Scoring Sheet'!R[-4]C[38]:RC[38](" & LR & "), ""<"" & R[-1]C )"
End Sub
This is what i get in the formula cell when I run the macro
这是我在运行宏时在公式单元格中得到的
=COUNTIF('Design Risk Scoring Sheet'!AN12:AN16(163), "<" & B15 )
It should ideally be AN163 instead of 16. I have tried removing RC[38] and putting AN instead but i get AN(163) which gives a #NAME error and if i remove the brackets in (" & LR & ") then I get single quotation marks in the formula :
理想情况下，它应该是 AN163 而不是 16。我尝试删除 RC[38] 并放置 AN，但我得到 AN(163)，它给出了 #NAME 错误，如果我删除 (" & LR & ") 中的括号，那么我得到公式中的单引号：
=COUNTIF('Design Risk Scoring Sheet'!AN12:'AN163', "<" & B15 )
I dont know how to fix this problem?
我不知道如何解决这个问题？
采纳答案by tigeravatar
Alternate:
备用：
Sub Date1()
Dim sDate As String
sDate = InputBox("Enter Date", "Date Entry", Format(Now, "m/d/yyyy"))
If Len(sDate) = 0 Then Exit Sub 'Pressed cancel
If Not IsDate(sDate) Then
MsgBox "[" & sDate & "] is not a valid date.", , "Exiting Macro"
Exit Sub
End If
Range("B15").Value2 = DateValue(sDate)
Range("B16").Formula = "=COUNTIF(AN12:AN" & Cells(Rows.Count, "AN").End(xlUp).Row & ",""<""&B15)"
End Sub
回答by kpark
Try This..
尝试这个..
Range("B16").FormulaR1C1 = _
"=COUNTIF('Design Risk Scoring Sheet'!R[-4]C[38]:RC[38](" & LR & "), < & R[-1]C )"
