Excel VBA Functions- Perform Calculation if Value is found to be within Specified Range
I have a workbook where I need to manage vacation accrual throughout a calendar year.
On the 1st of each month I need the accrued vacation hours amount (stored in column "E") to be added to the "Previous PTO BAL AVAIL" column (" I ") and then place the new amount in the "PTO Bal" column (" J ").
I'll have a PTO Balance column (col "J") which will contain the current vacation balance.
I'll have a cell entitled "TodaysDate" aka "C1" and a named ranged called "RenewalMonth" which will contain the 1st day of each month for the entire year.
I need the code to work as follows:
Find TodaysDate in the "RenewalMonth" range column "AA"
Add "hours accrued" (col "E") to the "Previous PTO Bal Avail"
Store the new balance to be in "PTO Bal" column "J"
**The tricky part, when a new month start I would need the amount stored in the "PTO Bal" column to now be stored in the "Previous PTO Bal Avail" column and then run through steps 1 - 3 again**
I attempted to record a Macro to see if I could figure out how to get this to work but I'm more confused now than before..