Type mismatch error

Hello

I want to use this worksheet function in the AfterUpdate event of textbox4 of userform frmCustomers

Please Login or Register  to view this content.

but this produced run-time error 13 "Type mismatch".

Stucked

I am not sure what is giving you this specific error but your COUNTIF call is incorrect. The syntax is

COUNTIF(range, criteria)

This is your call:

CountIf(Sheets("SoldOut").Range("B7:B1048576"), Sheets("SoldOut").Range("B7:B1048576") & "")

I don't understand what you are trying to do with that call. The second parameter is a range concatenated with a null string so will return the value in B7 as a string. I doubt that is what you intended.

The following examples of correct syntax:


CountIf(Sheets("SoldOut").Range("B7:B1048576"), Sheets("SoldOut").Range("C3"))


CountIf(Sheets("SoldOut").Range("B7:B1048576"), 5)



CountIf(Sheets("SoldOut").Range("B7:B1048576"), ">10")

Originally Posted by 6StringJazzer

I am not sure what is giving you this specific error but your COUNTIF call is incorrect. The syntax is

COUNTIF(range, criteria)

This is your call:

CountIf(Sheets("SoldOut").Range("B7:B1048576"), Sheets("SoldOut").Range("B7:B1048576") & "")

I don't understand what you are trying to do with that call. The second parameter is a range concatenated with a null string so will return the value in B7 as a string. I doubt that is what you intended.

I want to use the sheet level function in VBA.Here is the link of sheet level function.
http://www.excelforum.com/showthread...t=#post4574196

I hope this will help to know what I am asking help for.

Best Regards
Imran Bhatti

Now I see. The COUNTIF is within a SUMPRODUCT, and the COUNTIF contains arrays. I don't think VBA function evaluation will handle this the same way that the Excel worksheet functions handle it. The Evaluate function will more directly evaluate a formula exactly as Excel would evaluate it in a worksheet.

Try this:

Please Login or Register  to view this content.

Originally Posted by 6StringJazzer

Try this:

Please Login or Register  to view this content.

First of all thanks a lot for reviewing my long awaiting thread.
I would say it solved the problem 50%. Remaining is that it is summing for only Invoices which are consecutive for a a customer.i.e. in my newly attached workbook there are many invoices for Mis with some other invoices of other customers in between and the Evaluate code is summing only the top invoices for Mis.

Your help will be much appreciated.

Best Regards
Imran Bhatti

I correctly converted the sheet formula into VBA but your latest post indicates that the sheet formula is wrong to begin with. Since I didn't create it, I will have to see if I can figure out what it's doing.