Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Mar 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Tracking a running total (2002)

    I have numbers cells A1 through A5. I would like to increase or decrease those numbers by an amount I enter into their adjoining cells in B1 through B5. After that, I'd like to clear the entered numbers in B1 through B5 without losing the updated data in A1 through A5. I'm trying to develop a simple inventory spreadsheet that can track a running total for items. Can anyone help me out? Thanks

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tracking a running total (2002)

    What do you want to trigger the update of A1 through A5? Do you want to click on a button? Do you want it to be automatic when one of the particular cells is changed, and all of the cells B1 through B5 contain something? What do you want to happen if one of the cells B1:B5 contains a non numeric value? Do you want to keep a log of the updates on another sheet?

    The following code will look at the cells B1:B5 every time a change is made to any of those cells. If all of the cells contain numeric values, then the values are added to cells A1:A5 and B1:B5 are cleared. No log is kept, so there is no way to undo this. To enter this code, right click on the sheet tab where you want this to happen and then click on "View code" in the pop up menu. Paste the code into the VBE code window.

    <code>
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Intersect(Target, Range("B1:B5")) Is Nothing Then Exit Sub
    For Each oCell In Range("B1:B5")
    If Trim(oCell.Value) = "" Or Not (IsNumeric(oCell.Value)) Then Exit Sub
    Next oCell
    Application.EnableEvents = False
    For Each oCell In Range("A1:A5")
    oCell.Value = oCell.Value + oCell.Offset(0, 1).Value
    Next oCell
    Range("B1:B5").ClearContents
    Application.EnableEvents = True
    End Sub
    </code>
    Legare Coleman

  3. #3
    Lounger
    Join Date
    Mar 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tracking a running total (2002)

    This is just what I'm looking for. However, a button that would trigger the updates all at once would be nice. I plan on including a condition for the non numeric values and am not concerned about a log. Thanks

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tracking a running total (2002)

    Put this code in a standard module and attach it to the button:

    <code>
    Public Sub UpdateRunTot()
    Dim oCell As Range
    For Each oCell In Range("B1:B5")
    If Trim(oCell.Value) = "" Or Not (IsNumeric(oCell.Value)) Then
    MsgBox "B1:B5 contains one or more non-numeric values"
    Exit Sub
    End If
    Next oCell
    For Each oCell In Range("A1:A5")
    oCell.Value = oCell.Value + oCell.Offset(0, 1).Value
    Next oCell
    Range("B1:B5").ClearContents
    End Sub
    </code>
    Legare Coleman

  5. #5
    Lounger
    Join Date
    Mar 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tracking a running total (2002)

    I admit being an inept novice at this. I can't set the button up without getting error codes.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tracking a running total (2002)

    What error are you getting and when do you get it? When you attach the macro to the button? When you press the button? Did you put the code in a regular module, not in an event module (here I had you put the first code)? How did you attach the code to the botton? Did you use a button from the Control Toolbox or from the Forms Toolbox?

    If you can't figure it out by answering the questions above, then it might speed up getting an answer if you could attach a workbook that shows the problem you are having.
    Legare Coleman

  7. #7
    Lounger
    Join Date
    Mar 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tracking a running total (2002)

    I was afraid I'd get in over my head with this button thing. Your first code works fine for me so I think I'll just stick with it. I appreciate all your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •