VBA Help

[JakeaB]

Hello,

I am attempting to write code for an inventory management system for my company. I need help to write code to reduce my inventory by the amount requested. I have a continually expanding table based on the items added to the request, so the code will need to find all of the item ID's on the table and reduce the quantity on another table based on the correlating item ID. The two tables are on separate sheet tabs.

 

[mumps]

Hello and welcome to the Forum. It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshot (not pictures) of both sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[JakeaB]

Here is a sample of the sheet

Inventory Form Macros.xlsm

Shared with Dropbox

www.dropbox.com

 

[JakeaB]

Hello and welcome to the Forum. It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshot (not pictures) of both sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Inventory Form Macros.xlsm

Shared with Dropbox

www.dropbox.com

 

[mumps]

You want to reduce the values in column M in the Linked Inventory by the amount in column K in ScopeOfWork for each Item ID? Is this correct? What happens if there is zero inventory for an Item ID in the Linked Inventory?

 

[JakeaB]

You want to reduce the values in column M in the Linked Inventory by the amount in column K in ScopeOfWork for each Item ID? Is this correct? What happens if there is zero inventory for an Item ID in the Linked Inventory?

That is correct. No need to account for 0 inventory as there is a notice when selecting an item, for the amount currently in stock. Also we are tracking inventory based on EOQ so that we have base reorder levels. This is just a preliminary sheet so all stock values can be disregarded.

 

[JakeaB]

You want to reduce the values in column M in the Linked Inventory by the amount in column K in ScopeOfWork for each Item ID? Is this correct? What happens if there is zero inventory for an Item ID in the Linked Inventory?

Keep in mind that items can be continually added to the "tblList" Table on ScopeOfWork So the code would need to read all item ID's in the table

 

[mumps]

Try:

Sub UnpdateInventory()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, ID As Range, fnd As Range
    Set srcWS = Sheets("ScopeOfWork")
    Set desWS = Sheets("Linked Inventory")
    For Each ID In srcWS.Range("H6", srcWS.Range("H" & Rows.Count).End(xlUp))
        Set fnd = desWS.Range("A:A").Find(ID, LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            fnd.Offset(, 12).Value = fnd.Offset(, 12).Value - ID.Offset(, 3).Value
        Else
            MsgBox ("Item Id " & ID & " not found.")
        End If
    Next ID
    Application.ScreenUpdating = True
End Sub

 

[JakeaB]

Try:

Sub UnpdateInventory()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, ID As Range, fnd As Range
    Set srcWS = Sheets("ScopeOfWork")
    Set desWS = Sheets("Linked Inventory")
    For Each ID In srcWS.Range("H6", srcWS.Range("H" & Rows.Count).End(xlUp))
        Set fnd = desWS.Range("A:A").Find(ID, LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            fnd.Offset(, 12).Value = fnd.Offset(, 12).Value - ID.Offset(, 3).Value
        Else
            MsgBox ("Item Id " & ID & " not found.")
        End If
    Next ID
    Application.ScreenUpdating = True
End Sub

It works perfectly! Thank you so much!

 

[mumps]

You are very welcome.