Countifs Macro

[DanMurphy]

Hi Everyone.

I know my way around excel sheets, but am a relative newbie to VBA in Excel.

I have a formula with a lot of countifs. It returns a result based on three criteria on a different sheet, one of which is a date range.

The formula in the spreadsheet works perfectly, the red color are my criteria.

=COUNTIFS('Current Projects'!I18:I150, ">=01/08/2010", 'Current Projects'!I18:I150, "<=31/08/2010", 'Current Projects'!B18:B150, PPI,'Current Projects'!K18:K150, "*UK*")

What I am trying to do is make a custom function that will fill all this easily, so that I am not doing a lot of copy+pasting.

This is the code I pulled together, but it is not working and I think I am going about this the completely wrong way.

Please Login or Register  to view this content.

I think my problem is the double quotes on ValDateStart and ValDate, but the code keeps erroring unless they are there, but doesn't run while they are present.

Any insight you can offer would be greatly appreciated.

[Andrew-R]

The problem is you're trying to address a VBA macro as if it were a worksheet function.

Try:

Please Login or Register  to view this content.

That should be closer, but I haven't tested it.