The attached macro will run in a workbook with a sheet named "sheet1" and col A as CODE, col B as IN qty , col C as OUT qty col D as balance (what you requested) and col E usage flag.
No prior cleaning code has been inserted. It could be attached after your approval of the code
The main idea is that for every code it starts at the beg and adds or substract qty from the balance until it reaches the end. It flags the row as it calculates the balance so it wont be taken into account by the loop.
Sub BALANCE()
Set sh1 = Sheets("sheet1")
r = 2
While sh1.Cells(r, 1) <> ""
If sh1.Cells(r, 5) <> "**" Then
ky = sh1.Cells(r, 1)
bal = 0
With sh1.Range("a:a")
Set c = .Find(ky, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
cr = c.Row
sh1.Cells(c.Row, 4) = bal + sh1.Cells(c.Row, 2) - sh1.Cells(c.Row, 3)
bal = sh1.Cells(c.Row, 4)
sh1.Cells(c.Row, 5) = "**"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End If
r = r + 1
Wend
LinkBack URL
About LinkBacks
Reducing of materials
Register To Reply
Bookmarks