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!
Example image:
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!
Example image: