New VBA user - H E L P

[loveexcel21]

I am creating a spreadsheet for inventory use. I want to have a running total in a set of cells based upon new and different numbers being entered into a different set of cells.


B8:G29 AND AA8:AF29 (are the cells that will have new/different numbers entered often throughout the day)
N8:S29 (will be the cells that I want the running total in)


I need N8:S29 to keep the new totals even after my macro clears the numbers from B8:G29 and from AA8:AF29

I have never written any VBS Code but I know that is what is needed so that N8:S29 doesn't revert back to the original numbers.

Can anyone help me with this code?

 

[Domenic]

The following code will keep a running total for each cell in N8:S29, where the running total for a cell is made up of the corresponding values in B8:G29 and AA8:AF29. Note that the code needs to be placed in the worksheet module (double-click the sheet tab and select View Code).

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Range("B8:G29"), Target) Is Nothing And Intersect(Range("AA8:AF29"), Target) Is Nothing Then Exit Sub
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    Dim Rng1 As Range
    Dim Rng2 As Range
    Dim ColumnOffset As Long
    Dim RowOffset As Long
    If Not Intersect(Range("B8:G29"), Target) Is Nothing Then
        Set Rng1 = Range("B8:G29")
    Else
        Set Rng1 = Range("AA8:AF29")
    End If
    Set Rng2 = Range("N8:S29")
    ColumnOffset = Target.Column - Rng1.Columns(1).Column
    RowOffset = Target.Row - Rng1.Rows(1).Row
    Target.Copy
    Application.EnableEvents = False
    Rng2.Cells(1, 1).Offset(RowOffset, ColumnOffset).PasteSpecial Paste:=xlPasteValues, operation:=xlAdd
    Application.EnableEvents = True
    Application.CutCopyMode = False
End Sub

 

[loveexcel21]

Dominic, I'm going to try it now! I'll reply again after I try it, but in the meantime, thank you so much!

 

[loveexcel21]

Dominic, Is there a way to have cells AA8:AF29 add to the cells in N8:S29 and B8:G29 subtract from them? The running totals are in N8:S29 and the sales are in B8:G9 while the additions to the inventory are in AA8:AF29.

Thanks again!

 

[Domenic]

Try...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Range("B8:G29"), Target) Is Nothing And Intersect(Range("AA8:AF29"), Target) Is Nothing Then Exit Sub
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    Dim Rng1 As Range
    Dim Rng2 As Range
    Dim ColumnOffset As Long
    Dim Op As String
    Dim RowOffset As Long
    If Not Intersect(Range("B8:G29"), Target) Is Nothing Then
        Set Rng1 = Range("B8:G29")
        Op = "Subtract"
    Else
        Set Rng1 = Range("AA8:AF29")
        Op = "Add"
    End If
    Set Rng2 = Range("N8:S29")
    ColumnOffset = Target.Column - Rng1.Columns(1).Column
    RowOffset = Target.Row - Rng1.Rows(1).Row
    Target.Copy
    Application.EnableEvents = False
    If Op = "Add" Then
        Rng2.Cells(1, 1).Offset(RowOffset, ColumnOffset).PasteSpecial Paste:=xlPasteValues, operation:=xlAdd
    ElseIf Op = "Subtract" Then
        Rng2.Cells(1, 1).Offset(RowOffset, ColumnOffset).PasteSpecial Paste:=xlPasteValues, operation:=xlSubtract
    End If
    Application.EnableEvents = True
    Application.CutCopyMode = False
End Sub

 

[loveexcel21]

Domenic, It works PERFECTLY. Thank you SO very much!