Remaining balance

[TG2812]

Hi,

I’m really struggling to calculate the remaining balance for a given country, product range, warehouse and production month and incorporate in my existing code (see below).
Each time the user is doing some changes in the worksheet “Main”, they are listed under the worksheet “Tracking”. What the code is doing is that it lists the previous and new values of a given cell and calculate the changes in quantity. Now I need to calculate the remaining balance and ensure it equals to 0.

Example: I increase my production of Peanut134SAZ by 4 in March. I need to reduce the Butter23 by 2 and Toast98YT by 2. My remaining balance equals 0 as the surplus created by Butter134SAZ has been taken from other references.

[TABLE="width: 738"]
<tbody>[TR]
[TD]Reference
[/TD]
[TD]Country
[/TD]
[TD]Product Range
[/TD]
[TD]Warehouse
[/TD]
[TD]Production month
[/TD]
[TD]Previous Quantity
[/TD]
[TD]New Quantity
[/TD]
[TD]Changes
[/TD]
[TD]Balance
[/TD]
[/TR]
[TR]
[TD]Peanut134SAZ
[/TD]
[TD]Germany
[/TD]
[TD]Food
[/TD]
[TD]Cologne
[/TD]
[TD]Mar-19
[/TD]
[TD]2
[/TD]
[TD]6
[/TD]
[TD]4
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Butter23
[/TD]
[TD]Germany
[/TD]
[TD]Food
[/TD]
[TD]Cologne
[/TD]
[TD]Mar-19
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]-2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Toast98YT
[/TD]
[TD]Germany
[/TD]
[TD]Food
[/TD]
[TD]Cologne
[/TD]
[TD]Mar-19
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]-2
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you all in advance for your great help.

Code being used;


'Global variables
Dim oldAddress As String
Dim oldValue As String



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo err
Dim sSheetName As String
sSheetName = "Main"


If ActiveSheet.name <> Tracking Then
Application.EnableEvents = False




Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Range("A" & Target.Row).Value
Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = Range("B" & Target.Row).Value
Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Range("C" & Target.Row).Value
Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Range("D" & Target.Row).Value
Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = oldValue
Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(0, 5).Value = Target.Value
Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(0, 6).Value = Environ("username")
Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(0, 7).Value = Now
Sheets("Tracking").Hyperlinks.Add anchor:=Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(0, 8), Address:="", SubAddress:="'" & sSheetName & "'!" & oldAddress, TextToDisplay:=oldAddress
End If




err:
Application.EnableEvents = True
End Sub





Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo err
oldValue = Target.Value
oldAddress = Target.Address


err:
End Sub