issue with countif in vba
 

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

issue with countif in vba
 

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


--

HTH

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


"Xavier Minet" wrote in message
...
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

issue with countif in vba
 

Thanks.

"Bob Phillips" wrote in message
...
myFormula = "=COUNTIF(E2:E" & LastRow - 1 & ",""" & currentCDBID & """)"


--

HTH

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


"Xavier Minet" wrote in message
...
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