• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro for cumulative total, then zeroing out current period

Coloray

Coloray

New Member
excel example.jpg

Trying to construct a macro that could that would move the "current" employee billable amount into a "cumulative" cell (which would store a running total of billed time); then zeroing out the "current" cell allowing someone to then enter new hours worked for the next period without needing to manually calculate the current period plus the cumulative period, and then delete the current data.

Thanks!
 
Thanks for the input SirJB7. A new period is not dictated by dates or times, the actual spreadsheet could be updated daily, several times a week, etc. I would simply have a macro-enabled button that would execute the movement from current to cumulative, then zeroing out the hours column, which would also zero out the current column; I just need to find a way to retain the updated amount in the cumulative cell.

Thanks!
 
Hi, Coloray!
Try this assigning this code to the button (code for the worksheet class module):
Code: 
Sub CPUWarmingProcess_LiquidNitrogenRequired()
    Range("E2").Value = Range("E2").Value + Range("D2").Value
    Range("D2").Value = 0
End Sub
Regards!
 
That works beautifully, thanks much SirJB7!

Sub CPUWarmingProcess_LiquidNitrogenRequired()
Range("E2").Value = Range("E2").Value + Range("D2").Value
Range("D2").Value = 0
End Sub

How would I implement the (above) code to work for dozens of lines of totals?

Job1: EmpRate * Time = CurrentBilling = Cumulative Total Move Current to Cumulative, Zero out Time
Job2: EmpRate * Time = CurrentBilling = Cumulative Total Move Current to Cumulative, Zero out Time
Job3: EmpRate * Time = CurrentBilling = Cumulative Total Move Current to Cumulative, Zero out Time
Job4: EmpRate * Time = CurrentBilling = Cumulative Total Move Current to Cumulative, Zero out Time
Job5: EmpRate * Time = CurrentBilling = Cumulative Total Move Current to Cumulative, Zero out Time

etc...


Thanks!
 
Hi, Coloray!
Wanna have dozens of CPU getting warm? :)
Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.
Regards!
 
Hi SirJB7!

Thanks for the tip, here is the file. Any assistance would be greatly appreciated. Thank you.
 

Attachments

  • Sample.xlsm
    31.8 KB · Views: 10
Hi, Coloray!
In your uploaded file, which are the columns like D and E of your sample file? H:I but not sure.
Regards!
 
Hi, Coloray!

Give a look at this file:
https://dl.dropboxusercontent.com/u...od - Sample (for Coloray at chandoo.org).xlsm

This is the code:
Code: 
Option Explicit

Sub MultiCPUWarmingProcess_LiquidNitrogenRequired()
    ' constants
    Const ksWS = "Current Tasks "
    Const ksWeek = "WeekList"
    Const ksYear = "YearList"
    ' declarations
    Dim rngW As Range, rngY As Range
    ' start
    Set rngW = Worksheets(ksWS).Range(ksWeek)
    Set rngY = Worksheets(ksWS).Range(ksYear)
    ' process
    rngW.Copy
    rngY.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
    rngW.ClearContents
    ' end
    Set rngY = Nothing
    Set rngW = Nothing
    Beep
End Sub

Regards!

PS: BTW, thanks for adding a trailing space to the worksheet name, very nice gesture... :mad:
 
Thanks SirJB7, and sorry about the trailing space :(

The problem with what you came up with, is that it deletes my VLookup function, necessitating re-entry of the formula each time. I simply want to zero out the Hours Worked cells (column L), after the current amount (column H) is moved to the YTD costs (column I). Your first code segment worked beautifully, I just need to implement it on a sheet-wide scale. Thanks so much for any help!!! Your original code below:

Sub CPUWarmingProcess_LiquidNitrogenRequired()
Range("E2").Value = Range("E2").Value + Range("D2").Value
Range("B2").Value = 0
End Sub

E2= YTD Costs
D2= Current Costs
B2= Time (Hours)
 
Hi, Coloray!

I didn't notice formula at column H but it can be done anyway clearing related range in worksheet Billable. I thought you wanted to update column I, not L. For column L only, just download again the updated file from same previous link. This is the updated code:
Code: 
Option Explicit

Sub MultiCPUWarmingProcess_LiquidNitrogenRequired()
    ' constants
    Const ksWS = "Current Tasks "
'    Const ksWeek = "WeekList"
'    Const ksYear = "YearList"
    Const ksHour = "HourList"
    ' declarations
    Dim rngW As Range, rngY As Range, rngH As Range
    ' start
'    Set rngW = Worksheets(ksWS).Range(ksWeek)
'    Set rngY = Worksheets(ksWS).Range(ksYear)
    Set rngH = Worksheets(ksWS).Range(ksHour)
    ' process
'    rngW.Copy
'    rngY.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
'    rngW.ClearContents
    rngH.ClearContents
    ' end
    Set rngH = Nothing
'    Set rngY = Nothing
'    Set rngW = Nothing
    Beep
End Sub

I kept commented the code until you answer regarding worksheet Billable cleaning.

Regards!
 
Hi SirJB7!

Thanks for that! The only thing that is not being done now, is the current costs (Column H) are not being moved into a running cumulative (YTD Cost) (Column I). Do you know of a way to store cumulative costs in column I, while running the rest of the code???


Thanks!!!!!
 
SirJB7 said:
I didn't notice formula at column H but it can be done anyway clearing related range in worksheet Billable.
Click to expand...
SirJB7 said:
I kept commented the code until you answer regarding worksheet Billable cleaning.
Click to expand...
Hi, Coloray!
Have you read those? Uncommenting a few lines will do the job... but partially until you define what has to be done with the hidden worksheet.
Regards![/quote]
 
Hi, Coloray!
But you didn't tell us what do you want to do... or are you going to fix it up by yourself?
Regards!
 
Well, I'm probably not qualified to fix it by myself, not entirely sure how. The hidden worksheet is simply data related to employee with billable rates. (i.e., John Smith @ $30) This data allows the formula to work with data validation (name) * rate = (current costs).

Thanks!!!
 
Hi, Coloray!
You still don't define what should be done. If you don't know how to do it in Excel, don't worry, just describe what would you manually do and we'll try to take care of it.
Regards!
 
Thanks SirJB7

We are almost there... I just want to move an amount out of one cell, and have that amount added to a cumulative total. This needs to be done without deleting the formula in the first cell. Your macro does all this except the part about not deleting the source cell formula; while zeroing out the input cell "Hours" I believe the solution is in your 'commented out' code, however I'm unsure what part to make active.

To clarify:

EmpRate * Time = CurrentBilling (then move current to Cumulative without deleting formulas), Zero out Time for the next cycle.

Thanks again for your patience!!!
 
Yes, exactly! That works great, thanks Narayan, thanks SirJB7. I really appreciate the help with this!
 
Hi, Coloray!

Give a look at this updated file, at the same previous link:
https://dl.dropboxusercontent.com/u/60558749/Macro for cumulative total, then zeroing out current period - Sample (for Coloray at chandoo.org).xlsm

This is the final code, which handles cleaning hidden worksheet and error trapping in column H formulas, and adds and zeroes weekly and annual data in only 2 instructions which makes it more efficient than looping thru the whole range specially it holds a lot of entries (and if not, it's smarter ;)):
Code: 
Option Explicit

Sub MultiCPUWarmingProcess_LiquidNitrogenRequired()
    ' constants
    Const ksWS1 = "Current Tasks "
    Const ksWeek = "WeekList"
    Const ksYear = "YearList"
    Const ksHour = "HourList"
    Const ksWS2 = "Billable"
    Const ksBill = "BillTable"
    ' declarations
    Dim rngW As Range, rngY As Range, rngH As Range, rngB As Range
    ' start
    Set rngW = Worksheets(ksWS1).Range(ksWeek)
    Set rngY = Worksheets(ksWS1).Range(ksYear)
    Set rngH = Worksheets(ksWS1).Range(ksHour)
    On Error Resume Next
    Set rngB = Worksheets(ksWS2).Range(ksBill)
    On Error GoTo 0
    ' process
    rngW.Copy
    rngY.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
    rngH.ClearContents
    If Not (rngB Is Nothing) Then rngB.ClearContents
    ' end
    Set rngB = Nothing
    Set rngH = Nothing
    Set rngY = Nothing
    Set rngW = Nothing
    Beep
End Sub

What I don't know if in the hidden worksheet both columns A:B should be cleared or just only B. Now it's clearing both, if you want to clear only B, you only have to adjust BillTable range definition.

Just advise if any issue.

Regards!
 
Hi, Coloray!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
 
Hi SirJB7!

I was wondering if you could explain the process of assigning various rows/columns/cells certain variables/constants/etc. If I needed to alter your code, I just need to figure out how to re-assign your variables...

Thanks!!!

Your code is below for reference:
Code: 
Option Explicit

Sub MultiCPUWarmingProcess_LiquidNitrogenRequired()
    ' constants
    Const ksWS1 = "Current Tasks "
    Const ksWeek = "WeekList"
    Const ksYear = "YearList"
    Const ksHour = "HourList"
    Const ksWS2 = "Billable"
    Const ksBill = "BillTable"
    ' declarations
    Dim rngW As Range, rngY As Range, rngH As Range, rngB As Range
    ' start
    Set rngW = Worksheets(ksWS1).Range(ksWeek)
    Set rngY = Worksheets(ksWS1).Range(ksYear)
    Set rngH = Worksheets(ksWS1).Range(ksHour)
    On Error Resume Next
    Set rngB = Worksheets(ksWS2).Range(ksBill)
    On Error GoTo 0
    ' process
    rngW.Copy
    rngY.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
    rngH.ClearContents
    If Not (rngB Is Nothing) Then rngB.ClearContents
    ' end
    Set rngB = Nothing
    Set rngH = Nothing
    Set rngY = Nothing
    Set rngW = Nothing
    Beep
End Sub

EDITED (SirJB7)
When posting code do it from the associated icon in the bar (5th from the right), or as it's explicitly described at the top of the reply text box.
 
Last edited by a moderator:
Hi, Coloray!

In constants section are set the names of the worksheets and ranges on which is based the procedure:
ksWS1 is the visible worksheet
ksWeek, ksYear and ksHour are dynamic named ranges in ksWS1 worksheet
ksWS2 is the hidden worksheet
ksBill is a dynamic named range in ksWS2 workhseet
You can check their definitions with the Name Manager (Formulas tab, Defined Names group, Name Manager icon).

In declarations section are declared the variables for holding the 4 named ranges described upwards.

In start section are assigned the actual named ranges to the declared variables that will hold them. The rngB range is embedded within On Error clauses because it may be empty and hence retrieve an error when tried to be referenced.

In process section is where all the job is done: copy from weeks range, paste with addition on years range, clear hours range, and if exits clear bill range.

In end section are destroyed the variables referencing the actual named ranges.

And that's all. So if you need to change the worksheets name you should change the ksWS1/2 constant definitions. While you don't change the 2 worksheets structure you don't need to change any of the 4 dynamic named ranges that are defined in the workbook: WeekList, YearList, Hourlist and BillTable; if you do adjust the OFFSET instructions properly.

Hope it helps.

Regards!
 
You must log in or register to reply here.
Back
Top