Boobug2016
New Member
Good Morning Everyone,
I am currently working on an inventory form strictly to check in and out materials and keep track of when and how much was removed/added.
I Have one worksheet labeled "In_Out" and the other is listed "Product_Master". When a product is added to the "in_Out" worksheet I also need Column "G" of "Product_Master" to reflect the changes. I have over 1000 locations and some locations have multiple products.
can anyone help? I am new to VBA but I am pretty proud of what i have been able to accomplish up to now. I just cannot figure out this final stretch.
Here is the code... I have changed it a handful of times but I still cant get it right....
>>> use code - tags <<<
This is the IN_Out sheet that tracks when inventory is taken from and placed into inventory.
The Product_master that has all of the information. This is where I need the inventory to update. The only issue is i need a relation between the location, and material because multiple locations have multiple materials and some same materials are in multiple locations. I know the answer is probably realy easy but im just not getting it.
this is my in and out form. So we can all add and remove as we need to.
I hope this is enough information. Thank you all soooo much!
I am currently working on an inventory form strictly to check in and out materials and keep track of when and how much was removed/added.
I Have one worksheet labeled "In_Out" and the other is listed "Product_Master". When a product is added to the "in_Out" worksheet I also need Column "G" of "Product_Master" to reflect the changes. I have over 1000 locations and some locations have multiple products.
can anyone help? I am new to VBA but I am pretty proud of what i have been able to accomplish up to now. I just cannot figure out this final stretch.
Here is the code... I have changed it a handful of times but I still cant get it right....
>>> use code - tags <<<
Code:
Private Sub cmbadd_Click()
''''''''''''validate''''''''''''
If Me.txtproductid.Value = "" Then
MsgBox "please select the product", vbCritical
Exit Sub
End If
If Me.Cmbunit.Value = "" Then
MsgBox "please select the product", vbCritical
Exit Sub
End If
'''''''''''''' add data
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("in_Out")
Dim lr As Long
lr = Application.WorksheetFunction.CountA(sh.Range("A:A"))
sh.Range("A" & lr + 1).Value = lr
sh.Range("B" & lr + 1).Value = Me.cmblocation.Value
sh.Range("C" & lr + 1).Value = Me.txtproductid.Value
sh.Range("D" & lr + 1).Value = Me.TxtQtyin.Value + me.Txtqty.Value
sh.Range("E" & lr + 1).Value = Me.txtqtyout.Value - Me.Txtqty.Value
sh.Range("F" & lr + 1).Value = Me.Txtqty.Value
sh.Range("G" & lr + 1).Value = Me.Cmbunit.Value
sh.Range("H" & lr + 1).Value = Me.txtdate.Value
If Me.TxtQtyin.Value = "in" Then
sh.Range("D" & lr + 1).Value = "NA"
Else
sh.Range("D" & lr + 1).Value = (Me.txtout.Value - Me.Txtqty.Value + Me.txtin.Value) - Application.WorksheetFunction.VLookup(Me.cmblocation, ThisWorkbook.Sheets("product_master").Range("B:D"), 3, 0)
'''''''''' clear boxes
Me.cmblocation.Value = ""
Me.txtproductid.Value = ""
Me.Cmbunit.Value = ""
Me.TxtQtyin.Value = ""
Me.txtqtyout.Value = ""
Me.txtdate.Value = ""
This is the IN_Out sheet that tracks when inventory is taken from and placed into inventory.
The Product_master that has all of the information. This is where I need the inventory to update. The only issue is i need a relation between the location, and material because multiple locations have multiple materials and some same materials are in multiple locations. I know the answer is probably realy easy but im just not getting it.
this is my in and out form. So we can all add and remove as we need to.
I hope this is enough information. Thank you all soooo much!
Last edited by a moderator: