Home

Finding Annual Count

%3CLINGO-SUB%20id%3D%22lingo-sub-882528%22%20slang%3D%22en-US%22%3EFinding%20Annual%20Count%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-882528%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20the%20attached%20workbook%20i%20have%20to%20find%20the%20annual%20count%20for%20all%20the%20entries.%20I'm%20not%20able%20to%20create%20a%20function%20in%20VBA%20that%20will%20take%20into%20account%20all%20the%20cases.%20The%20annual%20entries%20are%20in%20the%20below%20format%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CSTRONG%3EENTRIES%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%3CSTRONG%3ELOGIC%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ex%20Visits%20Every%20%3CSTRONG%3EThree%20Years%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CP%3Edivide%20x%20by%203%20and%20return%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ex%20Visits%20Every%20%3CSTRONG%3ETwo%20Years%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CP%3Edivide%20x%20by%202%20and%20return%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ex%20Visits%20%3CSTRONG%3EEvery%20Year%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3Ereturn%20x%20as%20it%20is%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ex%20Visits%20Every%20%3CSTRONG%3ESix%20Months%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3Emultiply%20x%20by%202%20and%20return%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ex%20Visits%20Every%20%3CSTRONG%3EThree%20Months%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3Emultiply%20x%20by%204%20and%20return%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-882528%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-882750%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20Annual%20Count%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-882750%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20specific%20reason%20you%20are%20looking%20for%20VBA%3F%20if%20not%20%2C%20you%20can%20do%20this%20with%20a%20formula.%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20try%20this%20formula%20in%20B2.%26nbsp%3B%20If%20the%20input%20extends%20beyond%20A6%2C%20please%20uupdate%20formula%20accordingly.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DLEFT(A2%3AA6%2CFIND(%22%20%22%2CA2%3AA6))%2F(VLOOKUP(UPPER(IFERROR(LEFT(RIGHT(A2%3AA6%2CLEN(A2%3AA6)-FIND(%22Every%22%2CA2%3AA6)-5)%2CFIND(%22%20%22%2CRIGHT(A2%3AA6%2CLEN(A2%3AA6)-FIND(%22Every%22%2CA2%3AA6)-5))-1)%2C%22ONE%22))%2C%7B%22ONE%22%2C1%3B%22TWO%22%2C2%3B%22THREE%22%2C3%3B%22FOUR%22%2C4%3B%22FIVE%22%2C5%3B%22SIX%22%2C6%3B%22SEVEN%22%2C7%3B%22EIGHT%22%2C8%3B%22NINE%22%2C9%7D%2C2%2C%22FALSE%22)*SWITCH(RIGHT(A2%3AA6%2C6)%2C%22Months%22%2C1%2F12%2C1))%3C%2FP%3E%3C%2FLINGO-BODY%3E
vicasso
Occasional Contributor

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 Yearreturn x as it is
x Visits Every Six Monthsmultiply x by 2 and return
x Visits Every Three Monthsmultiply x by 4 and return

 

1 Reply

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))

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies