Excel VBA Functions- Perform Calculation if Value is found to be within Specified Range
Hi Experts,
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 ").
For Ex:
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:
1.
Find TodaysDate in the "RenewalMonth" range column "AA"
2.
Add "hours accrued" (col "E") to the "Previous PTO Bal Avail"
3.
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..
It appears to me like you still have a problem with your work flow.
1. Creating a running total of Balance C/F (Previous PTO Bal Avail) plus Current Additions (hours accrued) can easily be achieved with a worksheet function.
2. Adding the current additions to the balance C/F at the end of the month requires that ..
a) Balance C/F must be a constant value (can't be a formula), and
b) Current Additions must be reset to zero at that time.
Whereas the above seems to be without problem in your plan your named ranges RenewalMonth, probably also TodaysDate, appear to be part of a solution that doesn't work. They should be scrapped and substituted with a proper description of the intended work flow.
It seems that you would like the changes described in step 2 above executed at the change of the month. That is no problem. Imagine it as a program that carries out the modifications described in step 2 above. Your work flow must decide how this program is activated.
You appear to have visualized some sort of automation. Note that the process of automation is quite separate from that of effecting the changes. Therefore, you would, first of all, require a procedure that executes the changes. You can call that procedure whenever you want.
In the next step you arrange for it to be called automatically. The way to do this would be to run the automation procedure every time you open the workbook. That procedure would check when the month change was last done. If the current date isn't within the same month as the last update a new update would be run, else, nothing is done. This method would ensure that the update is run even if the first day of the month is a holiday. Modifications would be made whenever the workbook is first opened in a new month.
The problem is that you must keep a record of when the last update was run. This record can be made accessible to the user or hidden from him but, when all chips are down, it can be manipulated. If you are the only user being able to manipulate is an advantage. In other cases it may be not that. There never is any danger of gross falsification because the sum isn't changed, but you must look at your own circumstances to decide whether automation is really in your best interest as compared to manual updates.
Either way, the first step would be to get the code to do the modifications. I'll be happy to do that for you if you confirm that I understood your intentions correctly.
If this is the case there might be a problem with resetting the Current Additions (hours accrued) to zero. If this value is calculated using the worksheet formula setting it to zero would imply altering data at the source. You haven't shown us where that source might be.
@Faustulus
Yes, you understood my intentions and I appreciate your assistance in sorting through the spaghetti. :)
So checking to see if today's date is within the Renewal range in order to trigger the PTO Accrual formula wouldn't work?
I was looking to have it work as follows...
The formula/ code would check today's date to see if it's within column AA
(formula ie: IF( AND( TodaysDate >= {AA2} "1/1/13" , < {AA3} "2/1/13" ))
**THEN**
IF "True" update PTO balance by summing Accrued Hrs & Previous PTO Balance
IF "False" don't perform any calcuation.
This should be ran only once so I would need the ability to check to see if the Accrued PTO hours were added for the current month.
Kindest Regards,
Faustulus
I don't see the advantage of your suggestion. You need a range (say, January) and I don't understand where the row numbers for column AA should come from that I need in order to define January. Then, you need a record of that an update was done. This record must probably be specific to the January update, meaning you might have to keep 12 records.
My suggestion only has the date of the last update. A comparison is made between today's date and the last update date. If it isn't in the same month and update is made, the date of the last update is reset and will be within the same month until next month. I think this is very simple and foolproof. However, it is the method of automation we are discussing.
I had raised concerns that automation may not be in your best interest. I had also pointed out that you need a program that you can run automatically or manually. Discussing the best way to run it is bound to be fruitless while it doesn't exist.
I have pointed out that the program will require the accumulated hours to be reset and that no idea has been put forward on how that might be done. While that issue remains unresolved you won't ever have a program. I urge you to focus on getting the program first.
"Abys" Wallace
ASKER
Ok faustulus.. Have you had an opportunity to look at the attached z sample workbook to see how column AA is setup?
I'm seeking a formula to check to see if a new month has started if so sum 2 numbers if not do nothing...
With an automated feature to check to see if the update was done... Since it should be done once a month
Your column AA is at the core of your suggestion how to determine whether an update is needed or not. How and where do you suggest to record whether it has been done already?
"Abys" Wallace
ASKER
I see your point ... I guess if I'm using VBA I could have a code to run on the 1st of every month
I pieced together the following to run when the workbook is opened. It'll sum the Accrued Hours with the PTO Balance if it's the 1st of the month.
Option ExplicitPrivate Sub Workbook_Open()On Error Resume NextIf Day(Now()) = 1 Then Call PTOAccrualEnd IfEnd SubPrivate Sub PTOAccrual() Dim LastRow As Long Dim iRow As Long Dim iCol As Integer LastRow = 0 'sum code Workbooks("SampleVacationAccrual2.xlsm").Sheets("data").Select 'Find last row For iCol = 8 To 9 'Columns H:I iRow = Cells(65536, iCol).End(xlUp).Row If iRow > LastRow Then LastRow = iRow Next iCol With Application.WorksheetFunction 'Place row totals in column E For iRow = 1 To LastRow Cells(iRow, "J") = .Sum(Range(Cells(iRow, "H"), Cells(iRow, "I"))) Next iRow 'Place column totals in row after current last row For iCol = 8 To 9 Cells(LastRow + 1, iCol) = .Sum(Range(Cells(1, iCol), Cells(LastRow, iCol))) Next iCol End With'copy current PTO balance and paste into "Previous PTO balance" field Workbooks("SampleVacationAccrual2.xlsm").Sheets("data").Select Range("J3:J52").Select Application.CutCopyMode = False Selection.Copy Range("H3").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Workbooks("SampleVacationAccrual2.xlsm").SaveEnd Sub
This will only run if it's the 1st of the month preventing it from being ran multiple times during a month. I tested it by changing the "1" into a "5" and stepping through it.
Private Sub Workbook_Open()On Error Resume NextIf Day(Now()) = 5 Then Call PTOAccrualEnd IfEnd Sub
As an aside, I suggest that you amend the table you have in your Rules worksheet to look like the amended table you find in the attachment. This would enable you to use this formula in column I of your Data Sheet:-