Sep 30 2019 06:36 AM
In the attached workbook i have to find the annual count for all the entries. I'm not able to create a function in VBA that will take into account all the cases. The annual entries are in the below format
ENTRIES | LOGIC |
x Visits Every Three Years | divide x by 3 and return |
x Visits Every Two Years | divide x by 2 and return
|
x Visits Every Year | return x as it is |
x Visits Every Six Months | multiply x by 2 and return |
x Visits Every Three Months | multiply x by 4 and return |
Sep 30 2019 08:09 AM
Is there a specific reason you are looking for VBA? if not , you can do this with a formula.
You can try this formula in B2. If the input extends beyond A6, please uupdate formula accordingly.
=LEFT(A2:A6,FIND(" ",A2:A6))/(VLOOKUP(UPPER(IFERROR(LEFT(RIGHT(A2:A6,LEN(A2:A6)-FIND("Every",A2:A6)-5),FIND(" ",RIGHT(A2:A6,LEN(A2:A6)-FIND("Every",A2:A6)-5))-1),"ONE")),{"ONE",1;"TWO",2;"THREE",3;"FOUR",4;"FIVE",5;"SIX",6;"SEVEN",7;"EIGHT",8;"NINE",9},2,"FALSE")*SWITCH(RIGHT(A2:A6,6),"Months",1/12,1))