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

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


--

HTH

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


"Xavier Minet" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
>

Thanks.

"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> myFormula = "=COUNTIF(E2:E" & LastRow - 1 & ",""" & currentCDBID & """)"
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Xavier Minet" <[email protected]> wrote in message
> news:%[email protected]...
>> 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
>>
>>
>
>