Convert COUNTIFS formula to VBA

[ExcelRonin]

Lookingfor help to convert formulas to VBA.

I have used a combination of COUNTIF & COUNTIFS formulas to calculate the age of members from different departments
While it works it is slow to execute and cumbersome to manage. Sample Code

Please Login or Register  to view this content.

Is there a better way to streamline the formulas in VBA.

I have attached a example workbook.

cheers.

[TMS]

Looking at this on an iPad so can't see how you have defined your Named Ranges. Full column references MIGHT slow things down but, in theory, they shouldn't. That said, I'd still avoid them ... better to use Dynamic Named Ranges using INDEX and COUNTA. And they're not R1C1 type formulae, so delete that bit (just the R1C1).

[TMS]

Note that an 18 year old wil be included in both of the first two formulae.

[ExcelRonin]

Thanks for responding
The named ranges are indeed full column. The 18 year old error is noted, typo on my part.

I've not used INDEX before, so I'll look into that. I am under the impression that COUNTA did not play well with criteria?

Are you saying to replace FormulaR1C1 with Formula with the existing code or to use it with INDEX?.

cheers

[TMS]

Please Login or Register  to view this content.

Assuming no blanks in the range, a Dynamic Named Range would look something like:

Please Login or Register  to view this content.