VBA loop to search every cell value from one column in another one

[jadox]

Hey guys,

I would really appreciate some help with a VBA code that I'm trying to create.
I have the following data in one sheet:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Group[/TD]
[TD]Alias[/TD]
[TD]Fruit[/TD]
[TD]Qty[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Alias[/TD]
[TD]Fruit[/TD]
[TD]Group[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Apples[/TD]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ME[/TD]
[TD]Melons[/TD]
[TD]RED[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Plums[/TD]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MA[/TD]
[TD]Mango[/TD]
[TD]BLUE[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Melons[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]GR[/TD]
[TD]Grapes[/TD]
[TD]RED[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]Grapes[/TD]
[TD]43[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AP[/TD]
[TD]Apples[/TD]
[TD]BLUE[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]Kiwi[/TD]
[TD]45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PL[/TD]
[TD]Plums[/TD]
[TD]GREEN[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]Mango[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]Strawberry[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
What I need to do is to search for every item from Column C in Column I, and if it exists then copy the adjacent cells (H and J) to A and B. If it doesn't exist leave cells in columns A and B empty

For example:
Search for the first item in column C which is "Apples", in column I... It's not there, so let A and B empty and go to the next one: Plums. Plums is in Column I so, copy the values from column H and I into A and B.

I'm new to VBA so I'm grateful for any help that I can get!
Thanks!

 

[Scott Huish]

What's the name of the sheet and the range that your table is on?
I based my code on Column C being the longest, because that's the column with the values being looked up. How does that not work for you?

 

[jadox]

What's the name of the sheet and the range that your table is on?
I based my code on Column C being the longest, because that's the column with the values being looked up. How does that not work for you?

Ok, let's say that the example above is sheet2 that has the info from column C and D. They come from sheet1 by a consolidation macro, so depending on the information on sheet1 the size of columns C and D can vary. Tha info in M, N and O is in sheet3, which are constantly changing because new groups and aliases are asigned.

Cheers!

 

[Scott Huish]

OK, but you're looking up the values in the table based on Fruit, so shouldn't this column always be the longest? What do you mean the info is coming from Column C and D, you are only looking up on value: Fruit, per your original post.

 

[jadox]

Let's see... I have the following workbook:

Sheet1
It has lots of columns with lots of data that is constantly changing. But I'm only interested in 2 specific columns: Fruits and Qty columns, so I have a macro that copies these 2 columns into Sheet2.

Sheet2
I have the two copied columns from sheet1 and I apply a consolidation macro to them. Now I have is A,B (raw copied data), column C (Empty) and D and E which are the consolidation result.

Sheet3
I have a bunch of fruit names in Column A, then some aliases in B and group names in C.

What I want to do is:
In Sheet2 I need a macro to look in sheet3 at all the names of the fruits in column A and if it finds one that's in column D (The consolidated Fruit column in Sheet2) then copy the Group and Aliases. If not, leave it blank.

As I said, if I bring the columns from Sheet3 into Sheet2 your code works wonderfully, but I need to leave them in their separate sheet.

I hope I managed to make some sense
Cheers