Macro for creating multiple lists

Hi guys,

I've been trying to put together a macro for a while to do this, with no success (details below). I consider myself a pretty decent Excel user; I can't write pure VBA code, but using Record, a few Google searches, and a little wit I've been able to solve a fair amount of macro issues, except this one.

I want to write a macro for a spreadsheet used for managing orders. The orders are each one line, and call out a certain quantity of a 6 digit item number; there are some orders that call out the same item number.

ex.
Order Item Qty
1 111111 10
2 222222 10
3 111111 5

On a separate tab, I have an inventory sheet. Each line represents a "lot" of an item number, with a corresponding quantity. There are multiple lots of the same item number.

ex.
Lot Item Qty
AA 111111 5
BB 222222 5
CC 222222 10
DD 111111 10

What I'm aiming to do is set up a macro that will go to the inventory sheet and create a named list that consists of every lot for a given item number, and have that done for every item number. From there, I want to have a cell on the orders sheet that uses Data Validation to limit the cell's value to a drop down of the lots that match that row's item number. I would want the end result to look something like this.

ex.
Order Item Qty Lot Lot Qty
1 111111 10 DD 10
2 222222 10 CC 10
3 111111 5 AA 5

Keep in mind there's a couple hundred lines of orders, and a few thousand lines worth of inventory, so you can imagine how long this process takes manually, or even with V-Lookups (plus then you get overlapping lots). I can handle the v-lookups associated with the quantities, I just really need help with a macro that will create a list at every change in item number, and an a data valiation who's list name can be based on a cell's value, which fills down a whole column. Any help at all would be greatly appreciated; thanks guys.

-Mike

Martin,

Thanks for helping to get me started. I took a look at your code, and did my best to adapt it to the real sheet, but I'm hitting an error at this line:

ValidationString = Left(ValidationString, Len(ValidationString) - 1)

"Run-time error '5': Invalid procedure or argument"

I haven't had much luck searching for an answer... any thoughts on what might be going wrong? Also, something I realized that could have an impact: when I laid out my example I represented the Lot codes as an "AA" type format... they're actually 6 digit numeric codes, same as the item number. I was just trying to keep it clear at the time but I thought it might be worth mentioning. Thanks again.

Hey guys,

I tried mrice's fix; it made it past that line and now returns an error on this line instead:

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=ValidationString

Run-time error '1004': Application-defined or object-defined error

Also, I tried adapting your version to my needs, snb, and while it runs its course, it doesn't seem to return an output, so I believe I'm doing it wrong... do you think you could give me a brief explanation on it? I understand the jist of what it's doing, I think I'm just having trouble figuring out what each numeric value actually represents.

Attached, you can find an example of the final format I'm aiming for with example data in it, and all extraneous data removed... the columns with grayed out headings will contain data in the final version. Please note too, that if possible I would like to have the same validation for columns N, P,and R, because sometimes it takes more than one lot to fill an order and we can use up to 3. I know that might be asking a lot but I really appreciate it guys; thanks again.

OpenOrderFG_example.xls

mrice,

I'm still getting the same error at the same line.... Any thoughts?

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=ValidationString

"Run-time error '1004': Application-defined or object-defined error"

mrice,

I tried it; same error at the same line. However, it does enable validation in the first Lot column on the Orders sheet, but the only available value in the drop menu is the item number that corresponds to that line, not a lot number. Upon changing the following line though...

ValidationString = ValidationString & Sheets("FG Inventory").Cells(M, 2) & ","
End If

To...

ValidationString = ValidationString & Sheets("FG Inventory").Cells(M, 6) & ","
End If


... it validates to the Lot number, i.e. I the drop down contains the lots. It still results in the same error at the same line though... any more thoughts on what could be causing that? It's technically doing what it's supposed to, but I'm hoping to continue running another macro after it so I'd hate for it to stop due to the error. Let me know if you have any thoughts; thanks!

mrice,

Sure, here you go. I did change a few values based on their content for company privacy; I'm sure you can understand. Let me know if you have any luck with it.

mrice,

Sorry for not getting back a little sooner, been busy lately. I tried out your macro with the example I gave you; it works as you said. When I tried it with my true spreadsheet, I still got the same error at the same line.

I went through adding sections of my true spreadsheet to the example I gave you, and found out that the "Customer Name" column (Column A on the "Orders" sheet) must be causing the error. When I leave it as "Site 1", "Site 2", "Site 3" as I sent it to you, it runs just fine, but the second I add our true customer names it generates the error. The only variables I see possibly generating the error are the varying character lengths of the names, or the fact that some of the names include punctuation... Any thoughts?

Alright I can work around that. Thanks for all the help and patience along way here, I really appreciate it, plus you just saved me about 4-6 hours worth of work a week.

Got it working just how I'd like; thanks again.