VBA to change date to next quarter end

[brentgeist]

I'm a VBA novice. I'm writing a macro to roll data forward from one quarter to the next. I'm looking for VBA code to change the date value in a cell from one quarter-end to the next, Ex. 9-30-2017 to 12-31-2017.

I understand the EOMONTH() formula, but I want to change the date using the macro. I don't want the user to change the date except by running the macro.

Thanks in advance for your help.

 

[Joe4]

Welcome to the Board!

Make use of the DateAdd function in VBA. See: https://www.techonthenet.com/excel/formulas/dateadd.php

So, here is a User Defined Function that when fed any quarter ending date will return the next quarter ending date:

Function NextQuarter(qtrDate As Date) As Date
    NextQuarter = DateAdd("m", 3, qtrDate + 1) - 1
End Function