Inventory Assistance

Quote from Derk

The error on line
ii = Application.Match(lv, vendors.Columns(1), 0)
means there is a vendor code on the Master sheet that is not on the Vendors sheet. You should be able to locate it because there will be an #N/A error for the vendor name in the bad row of the Master sheet. (The sheet names can be changed as desired, but none of the sheets can be deleted without fouling up everything.) This error shows when the Order sheet is being prepared, so the program stops because it can't locate the vendor information it needs.

Hi Derk:

You may be correct, but there is something else, because the Vendor page was changed as per the private post, and whenI added a bunch of new information on the Inventory page, this is what the Master page looks like:

[Blocked Image: http://www.natt.net/temp/error.jpg]

Nevertheless, notice in cells C5 and C6, I hand entered a valid Vendor code "S" for southern, but the n/a did not go away, despite the fact that row 5 says:

=VLOOKUP(C5,Vendors,2,0)

And, the vendors page does have an entry for Southern. I think something else is going on.

So what is the proper sequence for the end user?

1. Make sure products page and vendors pages are complete.

2. Build Inventory page as needed. Click "Send to Data Base Button" when finished.

3. Goto Master page and hand enter values for Vendor Code, PC# and Case value.

4. Reclick "Send to Data Base" button on inventory page.

:thanx: EDR

Master Page Observation

I think I found something interesting, maybe intended on your end?

I re-downloaded your last sheet, entered in all new Inventory data, and clicked the button. It said "202 new items added, goto Master page .... "

I went to the master page to hand enter vendor codes, and product 55155 Bowman's Gin was still on the Master page, yet it's definitely not on the Inventory page or the AOM page?

I searched by name and number using "find" feature on both Inventory and AOM and nothing found. Also, since that was an example product for previous testing, and because I know we don't even carry it in our location, it's not in the inventory I built. Maybe we need some code that cleans out, or "refreshes" all products on the Master sheet when something is sent to it? But then again, would I have to re-enter all codes by hand every time? Probably not good.

Suggestions

Hi Derk:

If I may please ....

1. I would suggest placing the "push button" on a new separate sheet, all by itself. The reason is, both the products page and the inventory page may be pasted in from another spreadsheet, every quarter or so. I've noticed when I've pasted in the inventory I'm building on another sheet, it sometimes copies over the button or shows the older button (or copies older Macro code?). To prevent this from happening, the end user should be able to copy in an entire SHEET into the Inventory page and not have it affect any Macro related code. Therefore, perhaps the push button on its own sheet, by itself (nothing else) would be a wiser idea.

2. Assuming the AOM sheet is built first from the Inventory page, as soon as the button is pushed ... perhaps some code regarding the Master Sheet, to first check if the product it contains matches what's on the AOM sheet. If it sees a matching product on the AOM that it already has on the Master, leave it alone, with its current vendor code, PC#, etc. If it does not find a match, delete it. And, if it sees something new on the AOM page, print it in alphabetical order with the N/A showing, so I can go in and enter the write vendor information.

3. The program should not crash if there is no PC# or Case Value entered. As long as the right vendor code like "S" is entered and the N/A disappears, the code should work. Perhaps a rule that says if Case Value = "", then Case Value = 0. This way we cover ourselves with a zero value and order by the bottle in the worst case scenario. Granted, all case values should be entered appropriately, but I don't want the program to crash if there isn't one.

4. Also, on the Vendor page, while "Code" and "Vendor Name" columns are important for things to function properly, the program should not crash if the remaining columns (account numbers, phone numbers, contact names, etc.) are left blank. I'm not sure where the Type '13' mismatch keeps occuring, but after starting with a fresh copy of your latest version and re-entering all the data carefully, and getting rid of all "N/A" it still occurred when pressing the push button for the second time.

Thanks for your help and patience in this debugging process! :thanx: EDR

Quote from Derk

You have the sequence correctly stated.

The reason the master page didn't update when you entered the vendor code was probably because when you earlier had the error in the macro show up, it left the calculations in manual mode. Go to the Tools Menu>Options and click on the Calculation tab and set the calculations to automatic. (When the finishing touches are put on this project we can make the errors fail more gracefully.)

I actually would close the debugger, close the program, clear temp files and cache, then reopen the program and there was no change. My last round I was able to get rid of the N/A values, but even when all new produts had a valid vendor, PC# and case value, when I went back to click the button again, I got the type 13 error again. I really do think something else is going on, and I wish I could provide more information. I'm not sure what else I can do to help duplicate or debug this particular one?

Quote from Derk

As you noted, old products don't disappear from the Master page now on purpose. That was in case you later brought them back in you wouldn't have to enter the data again. You can either
1) delete a row with an old product by hand, or
2) have me modify the macro so it deletes all products that are not on an inventory somewhere.
I suggest you try 1) for awhile but the choice is yours.

If it's easier for you and since I'm a little more computer savy than the average person that works for our company, I will try 1. I don't want to take advantage. However, ultimately, to be more computer-dumby-proof, I think #2 would be the best approach (or as explained in my last post - suggestion).

Quote from Derk

Two remaining questions (at least).
1) Where will case proces be found if they are not to be just the bottle price * case size?
2) Now it may happen that the on hand total is less than Par, but the difference is less than half a case. What would you like to have happen? Now it will show an order of 0 cases (because of rounding to nearest case #). We could force it to be 1 or not show it or show the number of bottles (as is done now when par is less than case size).

1. This question also related to your previous question some time ago about where will case discount prices be found. Great question! The way this works is, the prices on the products page, are all individual bottle prices, discount included. What the vendor does is ADDS a "broken case charge" to each bottle you order when you do NOT order 6, 12, 24, etc. (whatever their case value is). So, for example, ABSOLUTE may have a broken case charge of $1.00 per bottle. If I only order 8 bottles (and not the full 12), on the invoice, the vendor adds $8.00 broken case charge. You do not need to worry about this. I'll deal with this on the invoice processing. The best thing for the code to do is multiply the unit price times the number of bottles. If you know case value is defined as 12 for Absolute, and we're ordering two cases, the value would be Unit Cost * 24 to get the Cost - and the case price should be correct, because each individual bottle price already assumes we're ordering by the case to get the discount.

2. I think in previous posts, I requested that in situations where less than 1 case was needed (assuming we order by the case, case value >0), we always ROUND UP to 1 case. If we're talking multiple cases, then we round to closest whole number. I would prefer this, if OK with you.

:thanx: You rock! I still really do appreciate your time, effort, and patience on this one.

Rounding Issues

In direct relation to very last post of mine surrounding rounding, here are some quotes from some previous posts of mine:

[INDENT]Example 2: If GRAND MARN case value=0, and Bar 1 has a par of .5, with on hand of .3., and Bar 2 has a par of .5, with on hand of .4, and Bar 3 has a par of .5, with On Hand of .1:

Total Par = 2 (1.5 rounded up to nearest whole number or bottle value, ceiling)

Total On Hand = .8

Amount to Order = 2 BT (1.2 bottles rounded up to nearest bottle amount since case=0).

ALWAYS ROUND UP WITH SINGLE BOTTLE VALUES (when case = 0)

3. As per previous, orders should round to the nearest case, when a case is defined. However, if the case value is > 0, the minimum case ordered must be 1. So, for example, if ABSOLUTE has a minimum par of 12, and has only 7 bottles on hand (one only needs to order 5 bottles), which is less than a case of 12. If rounded to the nearest (0), we wouldn't order any cases, which is NOT good. We need to order at least 1 case, and even though the on Hand would exceed the par by 5 bottles, that is OK.[/INDENT]

I realize some of my posts have been so long, it's easy to miss some of these things - my bad - at least I'm not requesting any new major features or functionality. Thanks again for everything. We're getting closer, I hope.

:thanx: EDR

Quote from Derk

Ok. The cases thing is fixed (sorry I overlooked your earlier specification). If par is less than a case, though I'm still going with bottles. OK? It's an easy thing to change if not.

If it's an easy change, I would prefer to order by the case, when case is defined. Only by the case when case is defined. I would think, theoretically, if you are adding up total pars from all areas and only comparing that value, that par would never be less than one case. However, even if it did occur, we should still order at least one case.

Thanks for everything else. I will check the rest of it tomorrow as well. Working for the rest of the evening.

:thanx: EDR

Toggle switch?

Hey Derk:

I have an idea, since the case issue you just raised and how you would round up if par under case is in question, how about a toggle switch (a simple input)

1 = my way, round up by case, as per my last post
2 = your way, round by bottle if par less than a case.

Something like that. This way, I can try both rounding ideas and see which works better, and don't have to bug you in the future or request a code change.

If this is an easy add-on, cool. If not, don't worry about it. I'm excited to try the latest version tomorrow, with all the recent fixes.

:thanx: EDR

Quote from Derk

As to putting the Button on a separate page, that is easily done. However, The Inventory Page and The Products page (and all the other pages) must not be deleted. The pages may be cleared, however, and new Inventory data and Product data copied and pasted to them. The macro depends on the sheets hidden names so deleteing a sheet and then adding a new one could break the code

Well understood! Thanks for the heads up. I will make sure the sheet names never change. EDR

Wow!

Derk:

I got a chance to play with it, and there is not one single problem. I realize the "toggle request" is still remaining, but your recent version is performing flawlessly. WOW, what a great program! I can't wait to start using it in real-time. It's going to cut my inventory time in half, if not more! Yeah!

:thanx: EDR

Spoke too soon?

As I'm playing with this, I added some quick on hand examples (while trying to break this thing - HA!).

I did get one more Error '13' type mismatch, when I clicked the "push button" after adding several more on hands.

I was trying to see if when On Hand is greater than par, the item would disappear from the order sheet. So far so good. When I entered 18 "On Hand" in one Area with a Par of 4, and pushed the button again, the error occurred, and on the order sheet it shows a:

#NUM!

... in the Order Amount column?

[Blocked Image: http://www.natt.net/temp/order_error.jpg]

Also, notice that BUTTERSCOTCH has an order value of zero? I was hoping that if none was needed to be ordered, that it would not appear on the order sheet.

It might be nice for the Cost-related columns on the right to be formatted to have a dollar sign and only two decimal points to the right.

I'm going to send you a private post with a link to download the sheet, as I'm using it, so you can see what I may be doing wrong, or it may help you debug it better. Thanks! EDR

Quote from Derk

Are you sure your calculations are in automatic?

Yes!

:thanx: EDR

We are closing in

Hi Derk:

Yes, we are closing in. Where is "top of the module?" Looking for a place on all pages to enter true and not finding a designated cell or area? What am I missing? Tks. EDR

B = Busch

Hi Derk:

If you notice on the private sheet I sent you, B=Busch is another vendor I added. Goto the Master page, and enter in a "B" for vendor code for Budwesier, and notice the N/A# does not go away?

Derk:

Thanks Derk! Latest version working fine now, with no issues! :thanx:

I screwed up majorly in that the proper AOM format does alphabetize, but by "type" of product. So, they list all the LIQUOR first in alphabetical order (same format otherwise), with a subtotal at the bottom, then a beer heading is listed, with all the beers, with a subtotal at bottom, then wines, etc.

Therefore, I'm now putting my foot in my mouth by asking for more, but if there is one more request (what I think is simple) that I can make I would be appreciative.

I think the easiest fix (if easy - if not forget about it, I will NOT be offended) is:

add one more column to the Master page. I will add one of the following references (will always be the same code) in that column for each product:

L = Liquor
B= Beer
W = Wine
NA = N/A Beverage (non-alcoholic)
MF = Miscellaneous Food
D = Dessert Alcohol

Then, depending on how each product item is categorized, hopefully the AOM sheet can list the products in alphabetical order, by category, with subtotals of "cost" and "On hand" for each category.

LIQUOR

xxxxx ABSOLUTE
xxxxx ABSOLUTE CITRON
xxxxx B&B
etc

Subtotal Liquor Cost $ XXX.XX

BEER

xxxxx BUD
xxxxx BUD LT
xxxxx AMSTEL

Subtotal BEER Cost $ XXX.XX

WINE

xxxxx Acacia
xxxxx Arancio Merlot
xxxxx Danzante Pinot Grigio
xxxxx Robert Mondavi Cabernet

Subtotal WINE Cost $ XXX.XX

etc.

The format is basically the same, just "enhancing it" with one more separation variable?

I sent you a private post with the latest version, with my data and see Master page with one more column on the right.

Please let me know your thoughts! :thanx: EDR