Matching up variables in VBA

[caliskier]

Remember in school on tests there was sometimes a matching section where you would have to match 25 definitions with 25 words or something like that. I need to match two different sets of a bunch of words in VBA just like that. I have a sheet that gives me say Apples, Oranges, Limes, Onions, Radishes, Carrots, Squash, Lettuce, etc. Say that is all in column B. In my VBA code I am writing formulas that go and open files that are titled RedApples, or GreenLimes, or OrangeCarrots, and stick an answer in say the adjacent column C where Apples, Lmes and Carrots is in B. What is it called when you need to match stuff up like that? I want, on the VBA side, the code to know that Apples goes with RedApples, Oranges goes with OrangeOranges, Limes goes with Greenlimes. What would the code be to do that? I am trying to learn the syntax for this, any help would be appreciated.

If I knew what it was called I could search for it better.
Thanks so much.

[solnajeff]

HI

I have attached a sample file using the FIND function and the Worksheet_Change event, the code is in the sheet.

Use the drop down to enter the desired fruit in the blue shaded box and all matching entries will pop up below.

Please let me know if you need something different.

Regards


Jeff

[caliskier]

Well, not exactly, I assume I am going to have to tell VBA that Apples goes with RedApples, but when I run my program it will surf through tons of different fruit in column B and when it sees Apples I want it to put RedApples next to it in column C, if it sees limes I want it to write greenlimes, but only when I run the code, not all the time.

See what I am doing is copying this code into a data pile I get and then I run the code, the code does an ton of other things. I am trying to teach myself how to do this, how to match stuff up in VBA without haveing to write 35 if statments. "if it's Apple, then RedApple, else if it is lime, then greenlime, else if it is orange, then orangeorange" There must be a more efficient way to do that.

[Tom Schreiner]

Well... This will do what you want but I wish I knew more about your project to provide a better answer...

Run CreateCollection
Run AccessYourCollection

Please Login or Register  to view this content.