Updating Stock Data from DetailProduct Sheet into Stock Data in Product Sheet

Question

iam very new to VBA excel and programming and i've been stuck with one problem.

ive 2 sheets, one is "Product" Sheet for product data and "inItemDetail" Sheet for product that we purchase.

in "Product" Sheet there are 7 columns (no, Product ID, Product Category, Product Name, Unit, Stock, and Ware House) - pict below enter image description here

in "inItemDetail" Sheet there are 10 columns (Trans ID, Invoice Num, Date, Supplier, Warehouse, Product ID, Product Name, Product Category, QTY, and Unit) - pict below enter image description here

and a vba form like this enter image description here

my problem: when i click "save" button, i passing the datas in the listbox into inItemDetail sheet. my question is, how to update stock with the same product id in "Product" sheet with Data QTY in "inItemDetail" sheet ("Product" Stock + "inItemDetail" QTY)?

i've tried to find the id first and update only 1 data using a formula and it works but i don't know how to update when the data is more than 1

this is my code for save button:

Private Sub saveTransButton_Click()
Dim DBDetailTransaction As Object
Dim DetailData As Long

Set DBDetailTransaction = Sheet10.Range("A80000").End(xlUp)

    If Me.transcationIDInputBox.Value = "" _
    Or Me.invoiceNumberInputBox.Value = "" _
    Or Me.dateTransactionInputBox.Value = "" _
    Or Me.dropDownSupplierTranscation.Value = "" _
    Or Me.dropdDownWarehouseTransaction.Value = "" Then

        Call MsgBox("Please Fill The Detail Transaction And Detail Items Field", vbInformation, "Detail Transaction Data Input")
        
    Else
        'Passing Data from input Box in Details Transaction Frame into Sheet 10
        DBDetailTransaction.Offset(1, 0).Value = "=ROW()-ROW($A$3)"
        DBDetailTransaction.Offset(1, 1).Value = transcationIDInputBox.Value
        DBDetailTransaction.Offset(1, 2).Value = invoiceNumberInputBox.Value
        DBDetailTransaction.Offset(1, 3).Value = dateTransactionInputBox.Value
        DBDetailTransaction.Offset(1, 4).Value = dropDownSupplierTranscation.Value
        DBDetailTransaction.Offset(1, 5).Value = dropdDownWarehouseTransaction.Value
        DBDetailTransaction.Offset(1, 6).Value = totalItemsTransInputBox.Value
        
        'Passing Data from list Box into Sheet 5
        For DetailData = 1 To Me.itemTransactionDataTable.ListCount - 1
            Sheet5.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Me.itemTransactionDataTable.list(DetailData, 0)
            Sheet5.Range("A" & Rows.Count).End(xlUp).Offset(0, 1) = Me.itemTransactionDataTable.list(DetailData, 1)
            Sheet5.Range("A" & Rows.Count).End(xlUp).Offset(0, 2) = Me.itemTransactionDataTable.list(DetailData, 2)
            Sheet5.Range("A" & Rows.Count).End(xlUp).Offset(0, 3) = Me.itemTransactionDataTable.list(DetailData, 3)
            Sheet5.Range("A" & Rows.Count).End(xlUp).Offset(0, 4) = Me.itemTransactionDataTable.list(DetailData, 4)
            Sheet5.Range("A" & Rows.Count).End(xlUp).Offset(0, 5) = Me.itemTransactionDataTable.list(DetailData, 5)
            Sheet5.Range("A" & Rows.Count).End(xlUp).Offset(0, 6) = Me.itemTransactionDataTable.list(DetailData, 6)
            Sheet5.Range("A" & Rows.Count).End(xlUp).Offset(0, 7) = Me.itemTransactionDataTable.list(DetailData, 7)
            Sheet5.Range("A" & Rows.Count).End(xlUp).Offset(0, 8) = Me.itemTransactionDataTable.list(DetailData, 8)
            Sheet5.Range("A" & Rows.Count).End(xlUp).Offset(0, 9) = Me.itemTransactionDataTable.list(DetailData, 9)
        Next DetailData
        
        'Call updateStockItemData
        Me.transcationIDInputBox = ""
        Me.invoiceNumberInputBox = ""
        Me.dateTransactionInputBox = ""
        Me.dropDownSupplierTranscation = ""
        Me.dropdDownWarehouseTransaction = ""
        Me.itemTransactionDataTable = ""
    End If
    Unload detailInItemForm

End Sub

Answer 1

Use Application.Match to locate the product by its ID. Note you have an inconsistency with both transcation and Transaction.

Option Explicit
 
Private Sub saveTransButton_Click()
    
    Dim wsProduct As Worksheet, wsItem As Worksheet, wsDetail As Worksheet
    Dim rngProdID As Range, rngQu As Range, rngDetail As Range
    Dim i As Long, n As Long, id As String, qu As Double
    Dim r, lstBox
    
    Dim inputs
    inputs = Array("transcationIDInputBox", "invoiceNumberInputBox", _
             "dateTransactionInputBox", "dropDownSupplierTranscation", _
             "dropdDownWarehouseTransaction", "totalItemsTransInputBox")
    ReDim v(0 To UBound(inputs)) ' input box values
               
    ' check input boxes except totalItems
    For n = 0 To UBound(inputs)
        v(n) = Me.Controls(inputs(n)).Value
        If v(n) = "" And n < UBound(inputs) Then
            MsgBox "Please Fill The Detail Transaction And Detail Items Field", _
                 vbInformation, "Detail Transaction Data Input"
            Exit Sub
        End If
    Next
    
    ' sheets
    With ThisWorkbook
        Set wsProduct = .Sheets("Product")
        Set wsItem = .Sheets("inItem")
        Set wsDetail = .Sheets("inItemDetail")
    End With
    
    ' get range for ProductID
    With wsProduct
        Set rngProdID = .Range("B6:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
        'Debug.Print rngProdID.Address
    End With
    
    ' Passing Data from input Boxes ito sheet inItem
    Dim rngItem As Range
    Set rngItem = wsItem.Cells(wsItem.Rows.Count, "A").End(xlUp)
    With rngItem.Offset(1)
        .Value = rngItem.Row
        For n = 0 To UBound(inputs)
            .Offset(, n + 1) = v(n)
        Next
    End With
    
    'Passing Transactaction to Details sheet
    Set lstBox = Me.itemTransactionDataTable
    Set rngDetail = wsDetail.Cells(wsItem.Rows.Count, "A").End(xlUp)
    With rngDetail.Offset(1)
        For n = 0 To lstBox.ListCount - 1
            For i = 0 To 9
                .Offset(n, i) = lstBox.List(n, i)
            Next
            id = lstBox.List(n, 0) ' A
            qu = lstBox.List(n, 8) ' I
            
            ' Find and update product quantity
            r = Application.Match(id, rngProdID, 0)
            If IsError(r) Then
                MsgBox id & " not found", vbCritical
                Exit Sub
            Else
                'Debug.Print r, id, qu
                Set rngQu = rngProdID.Cells(r).Offset(, 4)
                rngQu.Value = rngQu.Value + qu
            End If
            
        Next
    End With
        
    ' clear form
    For n = 0 To UBound(inputs)
        Me.Controls(inputs(n)).Value = ""
    Next
            
    Unload Me
End Sub