Worksheet Function COUNTIF

  • Thread starter Thread starter D.S.
  • Start date Start date
D

D.S.

In my code, I'm trying to return a count of instances (intJobCount) a value exists in worksheet column D. This value being set as variable strJobNumb. The following code is preventing my code from even starting. The variable strJobNumb will always be an alphameric beginning with a letter.

This line of code will stop me from running with a < Compile error: Expected list separator or ) >
intJobCount = Application.WorksheetFunction.CountIf(D:D, strJobNumb)

If I add quotes to the columns such as below, it will not run, and I will get a < Compile error: Type mismatch >

intJobCount = Application.WorksheetFunction.CountIf("D:D", strJobNumb)

Any Ideas?

D.S.
 
Hi D.S,

Try this .......

intJobCount = Application.WorksheetFunction.CountIf(Range("D:D"),
strJobNumb)

Cheers
Nigel

In my code, I'm trying to return a count of instances (intJobCount) a value
exists in worksheet column D. This value being set as variable strJobNumb.
The following code is preventing my code from even starting. The variable
strJobNumb will always be an alphameric beginning with a letter.

This line of code will stop me from running with a < Compile error: Expected
list separator or ) >
intJobCount = Application.WorksheetFunction.CountIf(D:D, strJobNumb)

If I add quotes to the columns such as below, it will not run, and I will
get a < Compile error: Type mismatch >

intJobCount = Application.WorksheetFunction.CountIf("D:D", strJobNumb)

Any Ideas?

D.S.
 
D.S. said:
This line of code will stop me from running with a
< Compile error: Expected >list separator or ) >
intJobCount = Application.WorksheetFunction.CountIf(D:D, strJobNumb)

If I add quotes to the columns such as below, it will not run,
and I will get a < Compile error: Type mismatch >

intJobCount = Application.WorksheetFunction.CountIf("D:D", strJobNumb)

Any Ideas?
Click to expand...

Neither D:D plain nor "D:D" would be recognizable as tokens resolving to
range references. Try something like

intJobCount = Application.WorksheetFunction.CountIf(Range("D:D"),
strJobNumb)

or variations qualifying Range("D:D"), such as Activesheet.Range("D:D").
 
Thank you Nigel, that's what I needed it. Works great.

D.S.


"Anonymous" <Nobody> wrote in message Hi D.S,

Try this .......

intJobCount = Application.WorksheetFunction.CountIf(Range("D:D"),
strJobNumb)

Cheers
Nigel

In my code, I'm trying to return a count of instances (intJobCount) a value
exists in worksheet column D. This value being set as variable strJobNumb.
The following code is preventing my code from even starting. The variable
strJobNumb will always be an alphameric beginning with a letter.

This line of code will stop me from running with a < Compile error: Expected
list separator or ) >
intJobCount = Application.WorksheetFunction.CountIf(D:D, strJobNumb)

If I add quotes to the columns such as below, it will not run, and I will
get a < Compile error: Type mismatch >

intJobCount = Application.WorksheetFunction.CountIf("D:D", strJobNumb)

Any Ideas?

D.S.
 
Back
Top