Vba code issue....

[Boobug2016]

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 <<<

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!

 

[BobBridges]

I think it might help if you tell us what your program, in one of the versions that isn't working right, does do. It's usually easier to figure out what's wrong if we see "I want it to print the results on the next open line, but intsead it's putting it over the existing data on the last line"....or whatever.

 

[Boobug2016]

I think it might help if you tell us what your program, in one of the versions that isn't working right, does do. It's usually easier to figure out what's wrong if we see "I want it to print the results on the next open line, but intsead it's putting it over the existing data on the last line"....or whatever.

Sorry about that. I have been working on the same problem for over a week and I thought I had it solved but I do not.

I am creating an inventory system for my department because these materials cannot be added to our WMS. I have created a master sheet where you can add incoming Product to "New" locations. (We currently have 1389 locations and each location can hold multiple items with multiple sku's. We also have the potential to add 200+ more in the future)

The Product master works great. We are able to add items to new locations with no issue.

The area I am having an issue with right now is with the check in and out form to manage warehouse inventory. I have the form created but I cannot get it to "find" the Location and inventory to update the inventory. I also need the information to go to the sheet where you can see when, who, how much and location the materials were checked out in the inventory.


I have uploaded my data in hopes someone could help.

 

[BobBridges]

I used to do a lot with forms in Access, but that was some years ago and I've never done it in Excel, so I probably won't be much help. I do see you have three forms set up (very nicely, too), and in the Inventory-Management form you have lots of fields. What I don't see is any code for them. I think what's needed is for Excel to have program statements to run whenever an event is triggered in the form, such as when the operator enters a value in a field, or hits a button.

I don't see any code like that, but maybe it's only because I don't know where to look for it in Excel. How do I find it? (Or do you not know where to create it either?)

 

[BobBridges]

Ah, never mind, I did a little googling and found your code. So, again, rather than make me figure out the whole thing, tell me more: In which event and which part of the code is your program supposed to find the location and inventory? And what does it do now (instead of what you want it to do)?

 

[Boobug2016]

When you open developer and go to Visual basics, you will see the inventory management form on the left side panel. In this panel I would like to be able to "check" inventory "in and out".

What I need it to do: Populate info entered to the "in and out" sheet and also update the Product master sheet quantity for that location and item.

What it does now: Currently it just adds to the end of the in out sheet and does not edit the product master sheet quantity for the location, product and product id.

 

[BobBridges]

We may have to take this a few steps at a time. The first thing I see when I look at cmbadd_Click is that it's checking to be sure three of the fields in the form aren't blank. Ok, that's fine.

Then it fetches the in_Out worksheet, and counts the rows in column A; that's to find the last used row. I think the CountA function might betray you; it says here it counts the "non-blank" rows in a range, so for CountA to work for you, all the rows in col A must have something in them. If there's a blank row somewhere in the middle, I think your routine is going to write over the last used line rather than adding a new line to the bottom. Personally I do it differently; I wrote a function I call "LastRow" and I use it in many programs. I'll show it to you if you want, but whatever works for you.

Next you copy the fields of the form to (what you hope is) the last row of in_Out. My only suggestion here is that instead of adding it to row lr+1 in each statement, add 1 to lr at the start and then use row lr in each statement:

lr = lr + 1
sh.Range("A" & lr).Value = lr
sh.Range("B" & lr).Value = Me.cmblocation.Value
sh.Range("C" & lr).Value = Me.txtproductid.Value
sh.Range("D" & lr).Value = Me.Cmbunit.Value
sh.Range("E" & lr).Value = Me.TxtQtyin.Value + Me.Txtqty.Value
sh.Range("F" & lr).Value = Me.txtqtyout.Value - Me.Txtqty.Value
sh.Range("G" & lr).Value = Me.txtdate.Value

But it works the way you're doing it.

One comment about that first statement: "Number ID" is just a row number (and perhaps not even the correct row), so why are you recording it? Especially, why are you planning to record it on the other worksheet, Product_Master? What use is it?

Ok, that brings us to your question. You want to put some code after this to find the corresponding product in the Product_Master worksheet and update the values there. There are lots of ways to find the proper row in a table; maybe the best way is the MATCH function, which you can use in Excel by itself, and in VBA is available as Application.WorksheetFunction.Match.

Next question: What value are you going to match on? I'm guessing the Product ID, right? But if that's the case, why are you making the operator enter, in the form, not only the product ID but also the location and unit? Are they not both unique to the product? What will you do if the operator enters product code 12F613 and unit "pallet"? Won't that mess up your program? Better you should make the user enter only what is needed to identify the unique product and let the program look up the rest in a table.

Next question: Suppose the operator enters a product ID that isn't on the master table? Do you want to call that an error, and display a warning message, or instead add it as a new line to the bottom of the master list?

Deal with this much and we'll continue from there.