Need Help with VBA Macro

I am trying to create an Excel VBA Macro that will calculate vacation time accrual.

Basically, I would like to run the macro to update the available PTO by calculating:  Accrued Balance = (Carried Forward + Accrued + Hrs this Period) - Used PTO.  

The problem is that I need to update the Accrued Balance when the calculation is complete.  This gives me a circular ref error.

I tried calculating the value to a new cell and then update the old value to complete the tasks.  

It is almost like this has to be multi sub tasks in the macro.  Just not sure how to do this.
PTO.png
sfletcher1959VPAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Store the result of Accrued Balance = (Carried Forward + Accrued + Hrs this Period) - Used PTO in a variable defined as Double. Then update the Accrued cell value form the variable.

If that doesn't work then please post your macro.
0
sfletcher1959VPAuthor Commented:
Not sure what "variable defined as Double" means
0
Martin LissOlder than dirtCommented:
I'm talking about a macro. Here's one you can use. It assumes that any row that you want to update has something in column A.


Sub Accrued()
Dim lngLastRow As Long
Dim lngRow As Long

lngLastRow = Range("A65536").End(xlUp).Row

For lngRow = 2 To lngLastRow
    Cells(2, 5).Value = Cells(2, 3).Value + Cells(2, 5).Value + Cells(2, 2).Value
Next
End Sub

Open in new window

0
Managing Security & Risk at the Speed of Business

Gartner Research VP, Neil McDonald & AlgoSec CTO, Prof. Avishai Wool, discuss the business-driven approach to automated security policy management, its benefits and how to align security policy management with business processes to address today's security challenges.

FaustulusCommented:
Hello sfletcher1959,
Actually, your column C isn't "Carried forward". It is "Brought Forward". Either column H or I contain the "Carried Forward" data. So,
B/F + Gained - Used = Available Balance = C/F
In the next cycle  the Available balance is carried forward and becomes the B/F balance. Since B/F is based on C/F and C/F is based on B/F you rightly get a circular reference. There is no way to resolve it.
Therefore you shouldn't produce the B/F balance by a formula. You need it to be a hard number. Such a number can either be entered manually or you can automate the process. At the press of a button you can move all C/F data to the B/F column while deleting the Gained and Used data at the same time. You probably need to do this once a month or even less frequently.
If you are interested in such a solution the easiest way for you would be to post your workbook and I will install the button for you.
You can remove any other sheets that might be in your workbook. You can also remove most of the rows. Just leave 5 or 6. On those you change the names to Peter, Paul and Mary. Just don't change the data types or the worksheet structure.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
In my code, Cells(2, 5).Value = Cells(2, 3).Value + Cells(2, 5).Value + Cells(2, 2).Value columns 2, 3 and 5 are added and placed in column 5, which replaces what was there. If they turn out not to be the proper columns or if you need to sum up more columns, that line is easily modified. Oh and I just noticed that the macro is hardcoded to only look at row 2!! It should be

Sub Accrued()
Dim lngLastRow As Long
Dim lngRow As Long

lngLastRow = Range("A65536").End(xlUp).Row

For lngRow = 2 To lngLastRow
    Cells(lngRow, 5).Value = Cells(lngRow, 3).Value + Cells(lngRow, 5).Value + Cells(lngRow, 2).Value
Next
End Sub

Open in new window

0
FaustulusCommented:
NB. The worksheet's structure would include any totals you might have at the bottom as well, of course, as any non-data rows at the top.
0
sfletcher1959VPAuthor Commented:
Not really what I was looking for, but of all the answers the best response.
0
FaustulusCommented:
Thanks for the points. I regret seeing you disappointed and would like to help more.
Therefore I read your original post once again where you say that you are trying to create a macro. Please bear in mind that it isn't possible to create such a macro removed from the workbook in which it is supposed to function.
As far as design help is concerned I have described the functionality as precisely as might be possible short of pointing to individual cells. If you would like help implementing the idea please post a workbook as described.
Best practice would be to post it in a new thread, but you can post a link to that thread here and all the experts who participated here will be sure to see it.
Regards,
Faustulus
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Software Firewalls

From novice to tech pro — start learning today.