Excel Manufacturing inventory

[Chrisg33]

Recently got promoted at my job to start and manage inventory. We have a set up excel sheet but currently have to manually update inventory when a product leaves. To keep it short for example product one uses 5 screws, 2 nuts, and one hinge. How would I make it so that if I enter that we sold 5 units of product one that it would remove the respective quantities of screws, nuts, and hinges in the main inventory page? Thank you for your time.

 

[KeepTrying]

Hi,
I updated my code. You need to open VBE (ALT+F11) and double click on Sheet name then add code. E.g.:

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)


Dim Rng As Range, i As Long, Screw_ As Long, Nut_ As Long, Hinge_ As Long, j As Long, Rng2 As Range
Dim ScrewSzorzo_ As Long, NutSzorzo_ As Long, HingeSzorzo_ As Long


Set Rng = Range("h2:h3")
Set Rng2 = ActiveSheet.UsedRange


If Not Intersect(Target, Rng) Is Nothing Then
    For i = 2 To Rng2.Rows.Count
        Select Case Target.Offset(0, -2) 'Rng2.Cells(i, 6)
            Case "Product01"
                ScrewSzorzo_ = 5 'change if necessary
                NutSzorzo_ = 2 'change if necessary
                HingeSzorzo_ = 1 'change if necessary
            Case "Product02"
                ScrewSzorzo_ = 4 'change if necessary
                NutSzorzo_ = 3 'change if necessary
                HingeSzorzo_ = 2 'change if necessary
        End Select
    Next i
    j = Target.Value
    Screw_ = ScrewSzorzo_ * j
    Nut_ = NutSzorzo_ * j
    Hinge_ = HingeSzorzo_ * j
    
    For i = 2 To Rng2.Rows.Count
        Select Case Rng2.Cells(i, 1)
            Case "Screw"
                Rng2.Cells(i, 3) = Rng2.Cells(i, 3) - Screw_
            Case "Nut"
                Rng2.Cells(i, 3) = Rng2.Cells(i, 3) - Nut_
            Case "Hinge"
                Rng2.Cells(i, 3) = Rng2.Cells(i, 3) - Hinge_
        End Select
    Next i
End If


End Sub

You can delete range J1:N3 on worksheet because these values are hard coded in VBA code above.

Regards,

 

[KeepTrying]

Any feedback please?