You're welcome! I just figured that if I simply supplies the formula, there would be some that would like to see how it came together, so I'd just take care of that right away!
Paul ----------------------------------------- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley ----------------------------------------- >________________________________ > From: Renton Cheung <rentonche...@gmail.com> >To: excel-macros@googlegroups.com >Sent: Wednesday, July 16, 2014 9:41 AM >Subject: Re: $$Excel-Macros$$ VBA to populate cells based on cirteria > > > >Hi Paul. This works quite well. Such a solution with well explanation! >Thank you! > > > > >On Wed, Jul 16, 2014 at 11:07 PM, Paul Schreiner <schreiner_p...@att.net> >wrote: > > >> >>I used this formula in Cell E2: >> >>=IF(OR((COLUMN()-4)=$C3,MOD((COLUMN()-4)-$C3,$D3)=0),$B3,"") >> >>then copied it to the other cells. >> >>Basically, it uses two concepts: >>the "Data" portion occupies (4) cells. A:D, so >>the column for year 1 is in column E. >>so, column() - 4 gives the "Year Number" of 1 for E, 2 for F, and so on. >> >>So, if the (column()-4) = the "Initial Year", then it inserts the value from >>B. >>=IF((COLUMN()-4)=$C3,$B3,"") >>Now, if to find if a given year is a multiple of the frequency offset by the >>initial year, I used: >> >>=IF(MOD((COLUMN()-4)-$C3,$D3)=0,$B3,"") >>This uses the same "column offset" calculation: Column()-4 >>then subtracts the initial year value -$C3 >>This basically gives you the number of years since the initial value. >> >>the MOD() function basically takes this value and divides it by the frequency >>and returns the number left over. >>that is: for the first item, the initial year is 1 and the frequency is 2. >> >>So, looking at year #6 in column J, the column number is 10 >>column()-4 = 6 >>6-Initial Year (1) = 5 >>mod(5,2) is the same as 5/2 which is 2 with a remainder of 1, so mod(5,2) = >>1 >>I test to see if it =0 (mod is EXACTLY a multiple of the frequency from the >>initial year) >>Since it is not, nothing is entered. >>but if we look at year number 7, >>the column number is 11 >>11-4 = 7 >>7-1 = 6 >>mod 6,2 = 0 >>so the value for item A WOULD appear in year 7. >> >> >>I then combined: >>COLUMN()-4)=$C3 >>MOD((COLUMN()-4)-$C3,$D3)=0 >> >>within an OR() statement: >>OR((COLUMN()-4)=$C3, MOD((COLUMN()-4)-$C3,$D3)=0) >> >>and used THAT as the conditional statement of the IF() function. >> >>give it a try and see if it works for you. >> >>Paul >>----------------------------------------- >>“Do all the good you can, >>By all the means you can, >>In all the ways you can, >>In all the places you can, >>At all the times you can, >>To all the people you can, >>As long as ever you can.” - John Wesley >>----------------------------------------- >> >> >> From: Huard <rentonche...@gmail.com> >>>To: excel-macros@googlegroups.com >>>Sent: Wednesday, July 16, 2014 8:02 AM >>>Subject: $$Excel-Macros$$ VBA to populate cells based on cirteria >>> >>> >>> >>>Dear experts, I need your help to populate the cells in the attached >>>spreadsheet. >>> >>> >>> -- >>>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >>>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >>>https://www.facebook.com/discussexcel >>> >>>FORUM RULES >>> >>>1) Use concise, accurate thread titles. Poor thread titles, like Please >>>Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will >>>not get quick attention or may not be answered. >>>2) Don't post a question in the thread of another member. >>>3) Don't post questions regarding breaking or bypassing any security measure. >>>4) Acknowledge the responses you receive, good or bad. >>>5) Jobs posting is not allowed. >>>6) Sharing copyrighted material and their links is not allowed. >>> >>>NOTE : Don't ever post confidential data in a workbook. Forum owners and >>>members are not responsible for any loss. >>>--- >>> You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. >>>To unsubscribe from this group and stop receiving emails from it, send an >>>email to excel-macros+unsubscr...@googlegroups.com. >>> >>>To post to this group, send email to excel-macros@googlegroups.com. >>>Visit this group at http://groups.google.com/group/excel-macros. >>>For more options, visit https://groups.google.com/d/optout. >>> >>> >>> >>-- >>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >>https://www.facebook.com/discussexcel >> >>FORUM RULES >> >>1) Use concise, accurate thread titles. Poor thread titles, like Please Help, >>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not >>get quick attention or may not be answered. >>2) Don't post a question in the thread of another member. >>3) Don't post questions regarding breaking or bypassing any security measure. >>4) Acknowledge the responses you receive, good or bad. >>5) Jobs posting is not allowed. >>6) Sharing copyrighted material and their links is not allowed. >> >>NOTE : Don't ever post confidential data in a workbook. Forum owners and >>members are not responsible for any loss. >>--- >>You received this message because you are subscribed to a topic in the Google >>Groups "MS EXCEL AND VBA MACROS" group. >>To unsubscribe from this topic, visit >>https://groups.google.com/d/topic/excel-macros/gm_nFSSCr34/unsubscribe. >>To unsubscribe from this group and all its topics, send an email to >>excel-macros+unsubscr...@googlegroups.com. >> >> >> >> >>To post to this group, send email to excel-macros@googlegroups.com. >>Visit this group at http://groups.google.com/group/excel-macros. >>For more options, visit https://groups.google.com/d/optout. >> > > >-- >Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >https://www.facebook.com/discussexcel > >FORUM RULES > >1) Use concise, accurate thread titles. Poor thread titles, like Please Help, >Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get >quick attention or may not be answered. >2) Don't post a question in the thread of another member. >3) Don't post questions regarding breaking or bypassing any security measure. >4) Acknowledge the responses you receive, good or bad. >5) Jobs posting is not allowed. >6) Sharing copyrighted material and their links is not allowed. > >NOTE : Don't ever post confidential data in a workbook. Forum owners and >members are not responsible for any loss. >--- >You received this message because you are subscribed to the Google Groups "MS >EXCEL AND VBA MACROS" group. >To unsubscribe from this group and stop receiving emails from it, send an >email to excel-macros+unsubscr...@googlegroups.com. >To post to this group, send email to excel-macros@googlegroups.com. >Visit this group at http://groups.google.com/group/excel-macros. >For more options, visit https://groups.google.com/d/optout. > > > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
