variable as criteria in COUNTIF

  • Thread starter Charles L. Snyder
  • Start date
C

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
 
V

Vergel Adriano

Charles,

Try it this way:

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


I believe this way should also work:

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

Charles L. Snyder

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

Thanks

CLS
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top