Macro action for making suggestions to missing terms

**nryntema01** · 02-27-2015, 01:54 PM

My company uses Excel to write out rental orders. Sometimes they forget to add corresponding details. I am attempting to create a search and advise macro to work with excel. In the sense that if a client orders "A item" then "B item" should be there. If not the macro can inform the user that "B item" should be there too.

Any help would be great.

**JBeaucaire** · 02-27-2015, 02:10 PM

Many ways to crack this egg. The way I would do it is if an item is added to the order, I would have a "checklist" where I can see all the items that go together that item is listed as a part of. Then I would go through those items and check each one to see if it is on the order already and add it automatically if it is not.

**nryntema01** · 02-27-2015, 02:14 PM

Yeah, that would require thinking on the behalf of the person entering the data. I need something that can search the document for the key terms, and if it is missing the "response" to that term it lets you know. Like that "Item A" and "Item B" scenario. If it was just a handful of people that I was creating a reference document thats fine, but a revolving 40 employees can get tedious and frustrating. Hence automation.

**JBeaucaire** · 02-27-2015, 02:22 PM

Yes, I agree. As noted in my initial response I would have created a lookup sheet in advance that has grouped all the related items together and possibly coded them.

Please Login or Register  to view this content.

With this, possibly hidden, sheet it is easy to find any item in the list, check it's GROUP and then run through all the items in that group adding them to the order if needed.

**nryntema01** · 02-27-2015, 02:28 PM

Ok I see what you are saying. Ill check that route. Thanks

**JBeaucaire** · 02-27-2015, 06:08 PM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**nryntema01** · 02-28-2015, 11:09 AM

Oh IM not sure yet. Still working on it. Not sure how to attach that list, say, to a button? That will analyze the first page.

**JBeaucaire** · 02-28-2015, 11:40 AM

I would recommend automating the process through a Worksheet_Change event.

1) User enters a product on the order in a specific column
2) Macro activates itself and disable all other macros temporarily
3) Macro looks up the product added on the hidden product sheet and locates the group code
4) Macro takes the first item from the product sheet in that same group code and checks the order to see if it is there already
no - adds it to the order
yes - moves on
5) Macro continues checking all the items in that group until it has run through them all adding the ones needed
6) Macro turns other macros back on and aborts.

**nryntema01** · 02-28-2015, 11:56 AM

That's great. But I don't necessarily want it to add automatically. Is it possible for a window to pop up letting it know that it's "missing its group partner"?

**nryntema01** · 02-28-2015, 12:03 PM

http://www.ozgrid.com/VBA/run-macros-change.htm

I found this. I think this is kind of what you are talking about.

Keep in mind, I am kind of new to Excel, but not new to computer language and how it works.

**nryntema01** · 02-28-2015, 12:14 PM

What Im noticing about the WorkSheet_Change is that it is based on cell or a certain amount of cells that change. How can base something like a cell change, but with a word. I need it to be able to look for LAV and know that EQ should be there as well.

**JBeaucaire** · 02-28-2015, 12:17 PM

I can't imagine spending time to develop an ordering tool and not have it be the most user-friendly. Having users have to read and absorb the detailed info of a popup and then close the popup and then manually go add the same item to the order is my definition of non-user-friendly.

I would recommend you add required items automatically if they are required and missing, else not bother with this. If there is some rare moments where the required additional items are not really needed, the user would be able to delete them. Seems to me this would be the exception.

**nryntema01** · 02-28-2015, 12:51 PM

If it were automatic, Would the macro be able to added to the line item, including number of days, number of units, cost of the unit, and total cost*days?

My list compiles of about 20 different If/then scenarios.

Would you be available to look at our existing form? Maybe you can see what I am dealing with.

**JBeaucaire** · 02-28-2015, 05:03 PM

I'm up for reviewing your work and answering your specific questions as you do your work. Or are you looking for someone to do this work for your company as a turn-key solution?

**nryntema01** · 03-02-2015, 11:33 AM

I'd like to learn and do it myself. That way I'll know how to make updates for the future. Is there way to do it by word search and not just by searching certain cells? Because each sales person kind of has their own format, it's hard to say that EQ will always be in cell B17 and if LAVLAIER is in Cell B18 then EQ should be in B17. I need the sheet to be basically "scanned" for the word EQ and then scan for Lavalier and if it see EQ and not Lavalier, then a warning or a note or something should pop up basically saying, you should really add that in. I know it may sound backwards or counter productive to you, but I'm coming into a 10 year system. What happens to me is Im on site setting up a Lavalier and then founding
out last minute that an EQ was not ordered. Does that make better sense?