Avatar of "Abys" Wallace
"Abys" Wallace
Flag for United States of America asked on

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..
    Range("J6").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(OR(TodaysDate=R[-2]C[17],R[-1]C[17],RC[17],R[1]C[17],R[2]C[17],R[3]C[17],R[4]C[17],R[5]C[17],R[6]C[17],R[7]C[17],R[8]C[17],R[9]C[17],R[10]C[17],R[11]C[17],R[12]C[17],R[13]C[17],R[14]C[17],R[15]C[17],R[16]C[17],R[17]C[17],R[18]C[17],R[19]C[17],R[20]C[17],R[21]C[17]),SUM(R[-1]C[-5],R[-1]C[-1]),R[-1]C[-1])"
    Range("J6").Select

Open in new window


Thank you for your time in Advance!  :)
SampleVacationAccrual.xlsm
Microsoft ExcelVisual Basic ClassicVB Script

Avatar of undefined
Last Comment
"Abys" Wallace

8/22/2022 - Mon
javaftper

What is the password to access the sample spreadsheet?
"Abys" Wallace

ASKER
by: javaftper -  my apologies ... the unprotected workbook is located below...

thank you
SampleVacationAccrual.xlsm
Faustulus

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
"Abys" Wallace

ASKER
@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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Faustulus

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 Explicit

Private Sub Workbook_Open()

On Error Resume Next

If Day(Now()) = 1 Then
 Call PTOAccrual
End If

End Sub

Private 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").Save

End Sub

Open in new window


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 Next

If Day(Now()) = 5 Then
 Call PTOAccrual
End If

End Sub

Open in new window

SampleVacationAccrual2.xlsm
Faustulus

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:-
=VLOOKUP(E3,Rules!$E$2:$F$6,2)

Open in new window

If you like the idea you can just copy the entire worksheet to your project.
130104-Rules.xlsx
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
Faustulus

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
"Abys" Wallace

ASKER
Hi Faustulus...  I apologize about you not receiving your points as I thought this was accepted days ago when I 1st reviewed your recommendation.

I appreciate your time and the work you did to get me going!  

Kindest Regards,

Tonya~