issue with countif in vba

[Xavier Minet]

Hi,

I am trying to identify duplicate values in an Excel 2003 column. If I am
using the following code:

myFormula = "=COUNTIF(E2:E" & LastRow - 1 & ",""=" & currentCDBID & """)"
countDUP = Evaluate(myFormula)
If countDUP > 1 Then ... rest of my code...

countDUP always returns "0" as result of the evaluate call (and in my
spreadsheet there are obviously duplicate values)

I thought I spotted the cause of the issue: countif requires a ";" as
separator between the range and the criteria. So I transformed my code into:

myFormula = "=COUNTIF(E2:E" & LastRow - 1 & ";""=" & currentCDBID & """)"
countDUP = Evaluate(myFormula)
If countDUP > 1 Then ... rest of my code...

And now countDUP always contains "Error 2015" which refers to a type
mismatch.

Does anyone know what is the cause of my problem ?

Any help would be appreciated.


Xavier

 

[Bob Phillips]

myFormula = "=COUNTIF(E2:E" & LastRow - 1 & ",""" & currentCDBID & """)"


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Xavier]

Thanks.

myFormula = "=COUNTIF(E2:E" & LastRow - 1 & ",""" & currentCDBID & """)"


--

HTH

RP
(remove nothere from the email address if mailing direct)