Search for strings

[David494]

If I use the line of code (not using vba).......
=COUNTIF(R1C1:R36C1,"COUNCIL TAX - General queries")

........ then it works fine.

However if i put this into vba using a macro then it doesn't work.

Application.WorksheetFunction.CountIf(range("R2C1:R35C1"), "COUNCIL TA
- General queries*")


Totally stuck here,

Will keep trying, but does anyone understand this

Thanks Guys.

Dav

 

[JE McGimpsey]

From VBA Help (Range Property):

expression.Range(Cell1)

Cell1 Required. The name of the range. This must be an A1-style
reference in the language of the macro.

So try

Application.WorksheetFunction.CountIf(Range("A2:A35"), _
"COUNCIL TAX - General queries*")

 

[David494]

but i just get the same error message.

Compile Error

Expected: =

.....I don't know what this error is.

Thanks for helping

Dav

 

[David494]

Strange, it seems to work when I msgBox it.

E.g

MsgBox (Application.WorksheetFunction.CountIf(Range("A2:A35"), "COUNCI
TAX - General queries*")

 

[David494]

Strange, it seems to work when I msgBox it.

E.g

MsgBox (Application.WorksheetFunction.CountIf(Range("A2:A35"), "COUNCIL
TAX - General queries*"))

 

[JE McGimpsey]

In VBA, if you're not returning the value to a variable or method, you
don't use the parens (see VBA Help "Using Parentheses in Code"), so the
code would be

Application.WorksheetFunction.CountIf Range("A2:A35"), _
"COUNCIL TAX - General queries*"

but that invokes the method without any purpose. Why are you calling
CountIf if you're not going to use the result?

 

[David494]

Sorry for not getting back to you sooner, but i forgot all about thi
post. The message box does me fine, as i just want the user to kno
the number of times a string appears between two specified dates, then
they can note down this value.

Thanks for your help

Dav