How to Update Stock quantity if I sold items?

please anyone knows how to do this?

Hello Saad3000,

The attached workbook contains the following macros. The main macro adjusts the stock level based on the current stock less what has been sold. The other macro is in the Worksheet_Change() event which checks the entry and then calls the main macro.

Stock Level (main macro)

Function StockLevel(Item_Name As String, Qty_Sold As Double) As Variant
 
  Dim OnHand As Double
  Dim Result As Range
  Dim Rng As Range
  
    Set Rng = Worksheets("Sheet1").Range("Batteries")
    
    If Item_Name = "" Or Qty_Sold < 0 Then
       StockLevel = ""
       Exit Function
    End If
    
      Set Result = Rng.Find(What:=Item_Name, After:=Rng.Cells(1, 1))
        If Result Is Nothing Then
           StockLevel = ""
           Exit Function
        End If
        
      OnHand = Result.Offset(0, 1).Value - Qty_Sold
        If OnHand <= 0 Then
           Result.Offset(0, 1) = 0
           StockLevel = 0
        Else
           Result.Offset(0, 1).Value = OnHand
           StockLevel = OnHand
        End If
                     
End Function

Worksheet_Change() Event Macro

Private Sub Worksheet_Change(ByVal Target As Range)

  If Not Intersect(Target, Range("B2:B6")) Is Nothing Then
     Call StockLevel(Target.Offset(0, -1).Value, Target.Value)
  End If
  
End Sub

Sincerely,
Leith Ross

Hello Saad3000,

It is possible to do this with formulae but you would need to change your format. The macros are faster since the only run when needed. Every formula in the workbook will be run anytime a change is made to cell that contains a formula. While the formula is often easier, it isn't always the best approach. If you have questions about the code or what it does, just ask.

Sincerely,
Leith Ross