Solved

Excel VBA Functions-  Perform Calculation if Value is found to be within Specified Range

Posted on 2013-01-03
11
531 Views
Last Modified: 2013-01-10
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
0
Comment
Question by:"Abys" Wallace
  • 5
  • 5
11 Comments
 
LVL 4

Expert Comment

by:javaftper
Comment Utility
What is the password to access the sample spreadsheet?
0
 

Author Comment

by:"Abys" Wallace
Comment Utility
by: javaftper -  my apologies ... the unprotected workbook is located below...

thank you
SampleVacationAccrual.xlsm
0
 
LVL 14

Expert Comment

by:Faustulus
Comment Utility
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.
0
 

Author Comment

by:"Abys" Wallace
Comment Utility
@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,
0
 
LVL 14

Expert Comment

by:Faustulus
Comment Utility
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.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:"Abys" Wallace
Comment Utility
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
0
 
LVL 14

Expert Comment

by:Faustulus
Comment Utility
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?
0
 

Author Comment

by:"Abys" Wallace
Comment Utility
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
0
 
LVL 14

Expert Comment

by:Faustulus
Comment Utility
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
0
 
LVL 14

Accepted Solution

by:
Faustulus earned 500 total points
Comment Utility
The attached project sports the suggested improvement of the formula in column I of your Data worksheet and modified format of the Rules worksheet. BTW, I would name the range rather than referring to it as is, but the following works just as well with your original design.

I haven't used your column A:A. Instead, you will find a value in cell Data!J1. This is the date up to which the accrued hours are included in the PTO Balance (which is equal to the Prev. PTO balance). The date is set by the system and must always be the last day of a month. You can move this cell to another location by changing the value of the constant UpdateRange at the top of the CalcMan module.

Your workbook now has a custom document property (File/Info -> Properties -> Advanced Properties -> Custom. This property records the actual date of the last update. It has no function beyond giving this information. You can change the name of that property by changing the value of the constant UpdateProp at the top of the CalcMan module.

I strongly suggest that you delete the existing code in the code sheet of the Data worksheet because it can't run there. Since it serves no purpose nothing good can come from keeping it where it is, which isn't to say that it might not cause mischief.

You will find a much abbreviated event procedure in the ThisWorkbook code sheet. All remaining code is in the module CalcMan. At the Open event the procedure PTOBalanceUpdate is called. It establishes how many months have passed since the date in Data!J1. As long as the time is less than 1 month no action will be taken. Else, a message will inform you of how many months have lapsed and add the appropriate number of monthly accruals to the totals. The code doing that is largely the same as what you had before.

As I said earlier, you can manipulate the dates. It is essential for the action that the date in J1 should be the last day of any month. If it isn't the update may not occur on the first day of the month after next. If, as on January 1, you never open the workbook the update will run on any day thereafter. So, if you wish to add a few months' worth of updates while setting up your system you can just adjust the date in J1 to the date that matches your B/F data. The date in the property may serve as a check against misuse. Since the program will always set J1 to the ultimo of the previous month you may be able to tell by looking at both dates whether the date in the worksheet has been tempered with.

I hope you like this arrangement. Let me know if you need any changes.
131005-Vacation-Accrual.xlsm
0
 

Author Closing Comment

by:"Abys" Wallace
Comment Utility
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~
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now