Option Explicit
Sub Update_Inventory()
Starting_Inventory
Dim endrow As Long 'last row in range
Dim Title As String 'book title
Dim Mycell As Range 'Cell with sold item title
Dim MyCell2 As Range
Dim qtySell As Long 'Sell volume in current row
Dim i As Long 'row counter
Dim BuySell As Range
Dim MyRow As Long
Dim RemInv As String
Dim invred As Long
Dim Lastrow As Long 'row above sell transaction
endrow = Range("A" & Rows.Count).End(xlUp).Row
Set BuySell = Range("B1:B" & endrow).Find("Sell", LookIn:=xlValues)
If BuySell Is Nothing Then
Exit Sub
End If
For i = Range(BuySell.Address).Row To endrow Step 1
Title = Range(BuySell.Address).Offset(0, -1).Value
qtySell = BuySell.Offset(0, 2).Value
Lastrow = BuySell.Row - 1
For Each Mycell In Range("A1:A" & Lastrow)
Do While MyRow < BuySell.Row
If Mycell.Value <> Title Then
GoTo nextmycell:
ElseIf Mycell.Offset(0, 1).Value = "buy" Then
RemInv = Range(Mycell.Address).Offset(0, 6).Address
If Range(RemInv).Value = 0 Then
GoTo nextmycell:
ElseIf qtySell <= Range(RemInv).Value Then
Range(RemInv).Value = Range(RemInv).Value - qtySell
qtySell = 0
MyRow = BuySell.Row
GoTo NextSale:
ElseIf Range(RemInv).Value < qtySell Then
invred = Range(RemInv).Value
Range(RemInv).Value = 0
qtySell = qtySell - invred
GoTo nextmycell:
End If
End If
Loop
nextmycell:
Next Mycell
NextSale:
On Error GoTo ErrorHandler:
Set BuySell = Range("B" & i & ":B" & endrow). _
FindNext(BuySell)
Next
ErrorHandler:
End Sub
Sub Starting_Inventory()
Dim endrow As Long
Dim Mycell As Range
endrow = Range("A" & Rows.Count).End(xlUp).Row
For Each Mycell In Range("A1:A" & endrow)
If Mycell.Offset(0, 1).Value = "buy" Then
Mycell.Offset(0, 6).Value = Mycell.Offset(0, 3).Value
End If
Next Mycell
End Sub
Bookmarks