Solved: Insert set parts in list

[Meatball]

Let me first say Thank You to everybody in this forum for the great help they provide to all of us who are VBA Challenged. This is a great site.
Now, I have a project that I want to try to work out.
I start with a list of tools that a customer wants. In this list there may be several items that are tool Sets, socket sets, wrench sets, etc. I have a master list which has many sets already showing individual part numbers and descriptions. I want to make a macro that will look at the list from the customer, compare to my sets list, and when it finds a match, pull the list of individual parts and insert them into the customers list, moving eveything else down the appropriate number of rows.
I will probably have to make changes to my sets list depending on how the code is set up.
I expect this may take some time so I am looking for someone who is willing to basically work with me one on one and maybe spend a bit of time on this. I have not used Match in any of my projects, which I think will be needed here, so that will take a bit on my part to understand. There is no rush on this project. Before and after sheets are shown on the attached file.
So is there anyone out there who wants to take this on?

[Bob Phillips]

Where is the sets data?

[Aussiebear]

G'day Meatball, The workbook provided doesn't show any set lists, so it'll be an issue for anyone who wants to work on your project. Can you re post your workbook with the extra data please?

[Meatball]

Sorry, I did not realize you would need to see that list. The actual list is some 15000 lines long so I copied a few of the sets including the 2 in my sample to a new sheet for this project.

[Bob Phillips]

How do you know what items belong in the set, there is no common key to spot them with.

[Aussiebear]

I've added the new data to the initial workbook. However I've noticed that in the "Before" & "After" sheets you have columns seeking the Substituted Part# & Substituted MFG. I cannot see that in the new data.

[Meatball]

Thats why I thought the list would have to be changed somehow. Right now the bold type is my indicater. I take the customers list, get the set number, insert a bunch of blank rows under it, do find on the set list, copy down to the next bold line, then paste that into the customers list.

[Bob Phillips]

We could work with bold cells although it is far from ideal. How does it get bolded, do you do it manually? Perhaps more pertinent, how does that list get created?

[Meatball]

Bear, what we are working on now is an initial step in our process. At a later time the substitutions would be made by our purchasing group.

[Meatball]

xld, It is done manually. I usually get the info from the web and do some converting to put it in my list. I did not think we could use the bold so i thought either something at the bottom of each set or another column with the set part number beside the individual parts.

[Aussiebear]

So if we match the requested part description in column A with the description in your Master list, is it likely to find multiplpe instances of the same description?

[Meatball]

bear, yes that is possible

[Bob Phillips]

Try this

[Aussiebear]

Hmmm..... this is looking more like a proper database requirement rather than Excel.

[Aussiebear]

Try what XLD????

Edited by Aussiebear: My refresh did not show your workbook XLD. My Apologies

[Meatball]

xld, That works. Now I will need to tweak a bit. First a question, I noticed in several of the threads you have worked on you have the With Application-----. What does that do, or not do, or prevent.
Your code appears to fill in the qty for me but when the individual parts are included then the Set qty should be zero. I also would like to color code each set, easier to see that way. And the set liost will actually be a seperate workbook. Until I have time to study your code I am not sure if I can handle all these changes myself. If you would like, I can play with it for a couple of days and get back in touch. And by the way Thank You

[Meatball]

bear, I informed the company a year ago that they needed a database for this but ----. That is why I am trying to do as much as I can with macro's. After this is completed I should be in good shape to have the biggest part of the process automated.

[Bob Phillips]

That application stuff is to make it more efficient and remove the flashing, it turns off screenupdating and sets calculation to manual, resetting it at the end.

Here is an update, the first two points. I will leave the separate workbook to you.

[Meatball]

Thanks xld. I sure thought this was going to be a lot harder and longer to do. I think I can handle the seperate workbook, If not I will do another thread. For now I will mark this solved and study your code. Thanks again to everybody

[Meatball]

I have run into an unexpected problem with this thread. Can I take the Solved off or should I start a new thread.

[Aussiebear]

I have removed the "Solved" as per your request. I still think this is much better as a true relational database project rather than an Excel database. Perhaps if you post a new thread in the Access section of the forum, someone there will also assist you and then you'll be able to evaluate the benefits of either.

[Meatball]

bear, thanks. Unfortunatly I do not have access to a database so I will have to see if I can work this out with Excel. I think it is possible with a bit of work.
The problem I ran into is that on my set list there are a few very large sets which contain smaller sets. Some of the individual parts also show in several different sets so I am getting individual parts being broken out as sets and some sets that are getting broken out as though they are a lot bigger than they actually are because of hitting the wrong spot in my full set list.

[Meatball]

I have been working on this for a couple of weeks now and am unable to get it resolved. First I messed up by making changes to my spreadsheet before attaching it to the first post in this thread, and I have learned enough that I should have known better than to do that but I guess I was wrong :banghead: . Second I thought I could change the code to use a seperate workbook instead of a sheet in the workbook, wrong again:( . I have added a sheet to the original attachment which shows the correct setup of my form along with the code that xld came up with. I have also added a column to the set list which will get rid of the problem with smaller sets nested within larger sets. Actual filename of set list is "Z:\Gould Southern Info\Tool & Desc Lookup\Tool # & Desc Finders\ab. TOOL SET List"
Hoping someone can help to get this working and help me figure out why I could not.

[Meatball]

Almost forgot