Updating Inventory Value

[rharri1972]

I have an inventory sheet with my "on hand" qty in cell G5. For this particular supply I currently have a value of 11 on hand (in Stock). I have a VB form in which I will "receive" in this same supply when it has been ordered and this process will add what I have received to what is already showing on hand. Example: On hand = 11, stock level is to be 15, I have ordered 4 and when it comes in, I will "receive" it "into the system" so that the new "on hand" level shows 15 in cell G5.

Is it possible to change the value in cell G5 from the VB form without having to reference two different cells to do the math? Can I, just from hitting an "update" button, have cell G5 know what to update to?

any help would be fantastic. I have looked and looked for software to do what I need for my new business but none is to be found to do what i need.

Thanks!!

 

[portews]

I've not tested this due to the missing items below, but it should work.

Assuming:
Your form is "UserForm1"
Worksheet Name is "Inventory"
Received quantity field on the form is "tbReceived"
The unique field on the form is "tbCatID" and matches with column A in the worksheet
In the workbook, the quantity on hand is in column G

Add this into the button press code, somewhere before the userform is dismissed:

Dim Rng As Range
'Search in the A column in the Inventory worksheet
With Sheets("Inventory").Range("A:A")
    'You're looking for what's in the tbCatID textbox on the userform. Return Rng when you match. 
     Set Rng = .Find(What:=UserForm1.tbCatID.Value, _
                    After:=.Cells(.Cells.Count), _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)
    'if you get a match
    If Not Rng Is Nothing Then
        ' add the existing quantity to the received quantity and replace the existing value in column H of the row where you found the match
        .Cells(Rng.Row, 7) = .Cells(Rng.Row, 7) + UserForm1.Recived.Value
    Else
        'otherwise tell them you couldn't match the item number
        MsgBox "No Match found"
    End If
End With