(Solved) – VBA excel macro – While loop until a condition is met for every cell in a column


I would like some help with a piece of VBA code 🙂

Problem: Add ‘Periodicity’ values (with a nested if statement) to the ‘Draft date’ until the date is greater than or equal to the ‘Cut off date’. Repeat for all cells in a column.

Referring to the example image: In cell S28 (5/09/2019), it has applied the formula of ‘P28 14’ but the adjusted date (19/09/2019) is still less than the cut off date (of 25/11/2019). The macro should repeat this periodicity formula until the adjusted date reaches 28/11/2019 (5 additional iterations).

I have tried using a Do While loop where values in the ‘Change baseline’ loop while the value is still “Yes”… but I was not successful due to my lack of VBA experience.

Example Periodicity formula in cell S4:

=IF($G4="Weekly",P4 7,IF($G4="Fortnightly",P4 14,IF($G4="Monthly",EDATE(P4,1),IF($G4="2-Monthly",EDATE(P4,2),IF($G4="Quarterly",EDATE(P4,3),IF($G4="6-Monthly",EDATE(P4,6),"No"))))))

Example Periodicity formula in VBA:

"=IF(RC7=""Weekly"",RC[-3] 7,IF(RC7=""Fortnightly"",RC[-3] 14,IF(RC7=""Monthly"",EDATE(RC[-3],1),IF(RC7=""2-Monthly"",EDATE(RC[-3],2),IF(RC7=""Quarterly"",EDATE(RC[-3],3),IF(RC7=""6-Monthly"",EDATE(RC[-3],6),""No""))))))"

Thank you in advance for any help, please ask for any further clarification if needed!

Please refer to this example image

Leave a Reply

Your email address will not be published. Required fields are marked *