• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with stores inventory control with vba

No1Stez

New Member
Hello,
Can someone please assist with the attached spredsheet with the vba coding.
I have done it in a very simple in-out but not lookup which would be very useful.

Stuck with matching cell to cell but not look up match
 

Attachments

  • Stores Inventory.xlsm
    63.9 KB · Views: 17
After looking at CommandButton55555_Click, I guess what you want to do is modify it so it looks at each row in IN-OUT, looks up the corresponding product number in DATABASE, and either adds or subtracts the quantity there.

Some folks would just write it for you. I'm more a teach-a-man-to-fish kind of guy, myself. And since you've written this much yourself, I'm guessing you can learn to do the rest too.

Let's start with looping through the rows in IN-OUT. It looks like this:
Code:
For jr = 8 to 32
  vPart = Sheet1.Range("F" & jr) 'grab the part number
  vQty = Sheet1.Range("I" & jr) 'get the quantity
  vIO = Sheet1.Range("D" & jr) 'get "IN"/"OUT"
  Next jr
You can name the variables whatever you like; I use "j"-something for loop indices and "r" for the something part since it's rows I'm looking through. But this loop looks at every row in IN-OUT and snags a copy of each of the values you care about. Once you have this, the next thing you'll have to do is a) find the row for that part number in DATABASE, and on that row b) add or subtract the quantity.

With me so far? Oh, and by the way, I didn't test this code; if there are typos in it, beware!
 
Thanks Bob, much appreciated..
Can you explain how i find said row in DATABASE and then add or subtract the quantity please!
Cheers
 
Adding and subtracting is easy; you already know how to do that. Finding the row is a little trickier, but not much.

There are a couple ways of doing it. The hard way is to take the value you're looking for (vPart, as I called it above) and loop through the rows in DATABASE looking for a value in column B (or is it A?) that matches it. That might look like this:
Code:
Set ocs = Sheet15.Cells 'just for easier coding
For jd = 2 to 9999 'look through the first 10 000 rows of DATABASE
  ThisPart = ocs(jd, 2).Value 'get this row's part number
  If ThisPart = "" Then GoTo NoSuchPart 'part not found!
  If ThisPart = vPart Then Exit For 'this is the right row
  End If
If jd > 9999 Then GoTo NoSuchPart 'that part not in the first 9999 rows!
' The part you want is in row jd of DATABASE.

' We go here if a part entered in the IN-OUT database doesn't exist.
NoSuchPart:
  MsgBox "Part number " & vPart & ", which you entered on row " & jr & ", not found in DATABASE."
  End 'leave the program
That would work, and it has the advantage that you can look at it and see what it's doing. If there really are ten thousand rows in your database, eventually, though, it'll be slow. You can speed it up by loading DATABASE into an array and search that in the same way. But it's probably best to use the one of the search functions of Excel.

In fact, here's a better idea: Add a column to IN-OUT that displays the row corresponding to the part number in D. This has two advantages: a) You let Excel show you the row right away, so your program doesn't have to go through any gyrations like the above to find it, and b) it immediately tells the operator who enters the part numbers if he got one wrong, because an error will show up in that column. Use the MATCH function to show the row, like this I think:
Code:
=MATCH(F$8,DATABASE!B:B,0)
That'll display the row number in DATABASE, or an error if no match is found. Then your program is much simpler. Let's say the new column is in IN-OUT!K, so:
Code:
For jr = 8 to 32
  ' You no longer need to grab the part number at all; all you need is the row in DATABASE.
  vQty = Sheet1.Range("I" & jr).Value 'get the quantity
  vIO = Sheet1.Range("D" & jr).Value 'get "IN"/"OUT"
  vRow = Sheet1.Range("K" & jr).Value 'get the row in DATABASE
  ' Now you already know which row in DATABASE to add or subtract from.
  Next jr
 
Hi Bob,

Once again thank you for all your endeavours,
Sorry to be a nuisance, i have added the above code but don't know how to make it work.

Could you when you have the time add everything and test then send me the file for me to look at and hopefully learn what i should be doing?

Many Thanks
Stez
 
That was just a sample; I assumed you'd have to adapt it to your own details and coding style. And anyway, I'm more a teach-a-man-to-fish kind of guy; I'd rather explain it to you, and have you work out for yourself how to do it based on my explanation; then you'll really know how, and be able to do it next time.

Let's start with the helping column in IN-OUT. I see there's a column there labeled LOCATION; can you tell me what that's for? The formula is complicated enough that I didn't try to figure it out, but "location" sounds like it might be related to the sort of thing I'm talking about. The resulting values, though, don't look at all like a row number, so maybe it's for some other purpose entirely.

What I want to create in IN-OUT is a new column that has just a row number in it. Let's say it'll be in col K. If IN-OUT!F8 says "XYZ", and part "XYZ" is found on row 13 in the DATABASE worksheet, then IN-OUT!K8 will have the value 13. You follow? You would use the MATCH function to accomplish this.

This gets you two advantages. One is that if the operator enters an invalid part number, col K will immediately show that it's an error, so the operator will see it right away. The other is that your program doesn't have to search through DATABASE looking for a matching part number; it can go straight to row 13 (or whatever value it finds in col K) and do its thing there.

If the whole thing is clear to you now, great. If not, that's alright; let's get col K working (or whatever column you want to put it in), and we'll modify your program code next.
 
Back
Top