JohnBausz
03-31-2016, 03:08 PM
I have used a combination of COUNTIF & COUNTIFS formulas to calculate the age of members from 7 sites
While it works it is slow to execute and cumbersome to manage.
Sample code
Range("D10").FormulaR1C1 = "=COUNTIFS(SITE,""Botany"",AGE,""<=18"")"
Range("E10").FormulaR1C1 = "=COUNTIFS(SITE,""Botany"",AGE,"">=19"",AGE,""<=20"")"
Range("F10").FormulaR1C1 = "=COUNTIFS(SITE,""Botany"",AGE,"">=21"",AGE,""<=25"")"
Range("G10").FormulaR1C1 = "=COUNTIFS(SITE,""Botany"",AGE,"">=26"",AGE,""<=30"")"
Range("H10").FormulaR1C1 = "=COUNTIFS(SITE,""Botany"",AGE,"">=31"",AGE,""<=35"")"
Range("I10").FormulaR1C1 = "=COUNTIFS(SITE,""Botany"",AGE,"">=36"",AGE,""<=40"")"
The named ranges SITE & Botany are full column ranges. My first problem is that the sites may vary from time to time. Is there a way to have the formula look up a list to get the name for each site.
Is there a way to streamline the formulas in VBA. I'm keen to learn more about VBA
I have attached an example workbook.
Cheers
While it works it is slow to execute and cumbersome to manage.
Sample code
Range("D10").FormulaR1C1 = "=COUNTIFS(SITE,""Botany"",AGE,""<=18"")"
Range("E10").FormulaR1C1 = "=COUNTIFS(SITE,""Botany"",AGE,"">=19"",AGE,""<=20"")"
Range("F10").FormulaR1C1 = "=COUNTIFS(SITE,""Botany"",AGE,"">=21"",AGE,""<=25"")"
Range("G10").FormulaR1C1 = "=COUNTIFS(SITE,""Botany"",AGE,"">=26"",AGE,""<=30"")"
Range("H10").FormulaR1C1 = "=COUNTIFS(SITE,""Botany"",AGE,"">=31"",AGE,""<=35"")"
Range("I10").FormulaR1C1 = "=COUNTIFS(SITE,""Botany"",AGE,"">=36"",AGE,""<=40"")"
The named ranges SITE & Botany are full column ranges. My first problem is that the sites may vary from time to time. Is there a way to have the formula look up a list to get the name for each site.
Is there a way to streamline the formulas in VBA. I'm keen to learn more about VBA
I have attached an example workbook.
Cheers