Unable to get countIF property of worksheetFunction class

**Deamo** · 06-10-2010, 03:14 PM

I have a worksheet, where the user selects a csv file to open, then the macro runs comparing values between the two worksheets

the issue i run into, is that on the WorksheetFunction.CountIf line, i get the above error.

If i change the Vrng to Valraw.Cells, then it works fine (although it takes a while to run)
I tested this in excel 07 last night, which worked fine while it was using Vrng - it's excel 03 (at work) where it fails.

Please Login or Register  to view this content.

**DonkeyOte** · 06-10-2010, 03:24 PM

Could be a few things but given you get to the COUNTIF I presume Vrng is ok, in which case I suspect the issue will come down to the fact that Vrng is probably non-contiguous, ie:

=COUNTIF(A1:A10,"apple")

is ok whereas

=COUNTIF(A1:A3,A7,A9,"Apple")

is not.

**6StringJazzer** · 06-10-2010, 03:26 PM

I don't see a problem obvious to me but I would debug this by setting a breakpoint at the following line:

Please Login or Register  to view this content.

step past it one instruction, and then bring up the Immediate Window and then use the command

print Vrng.Address

and see if the result is what you expect.

As for your workaround, instead of using ValRaw as the argument, try ValRaw.UsedRange. It may speed things up.

**Deamo** · 06-10-2010, 09:35 PM

Thanks both of you for the help.
Does appear to be an issue with getting it to look at the non-contiguous range.

i've reset that problem line to..

Please Login or Register  to view this content.

even though the whole range is specified, only those cells that are visible are being compared.
May not be a great work around in the grand scheme of things.. but it works (until it fails that is ;P ).