variable as criteria in COUNTIF

[Charles L. Snyder]

Hi

This doesn't work (it runs, but gives a result of 0 instead of the
correct answer) when the user responds with 54161 (for example):

Sub HowMany()
Dim res As String
Dim myinput As String
myinput = InputBox("Enter the CPT Code...")
res = Evaluate("=CountIf(Ops, myinput)")
MsgBox (res)
End Sub

This does work:

Sub HowMany()
Dim res As String
Dim myinput As String
'myinput = InputBox("Enter the CPT Code...")
res = Evaluate("=CountIf(Ops, 54161)")
MsgBox (res)
End Sub

I've searched this group and googled, but can't find why or how to get
the VBA for COUNTIF to accept a variable as a criteria....

Any idea how to get around this ?

Thanks again

C L Snyder

 

[Guest]

Charles,

Try it this way:

res = Evaluate("=CountIf(Ops, " & myinput & ")")


I believe this way should also work:

res = WorksheetFunction.CountIf(Range("Ops"), myinput)

 

[Charles L. Snyder]

I think I tried every permutation of "=", "&", etc but that one - it
works perfectly!

Thanks

CLS