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]

You don't really need VBA for this. It can be done with formulas. These formulas are for XL2007 and higher.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Group	Alias	Fruit	Qty				Alias	Fruit	Group
2	BLUE	AP	Apples	23				ME	Melons	RED
3	GREEN	PL	Plums	23				MA	Mango	BLUE
4	RED	ME	Melons	12				GR	Grapes	RED
5	RED	GR	Grapes	43				AP	Apples	BLUE
6			Kiwi	45				PL	Plums	GREEN
7	BLUE	MA	Mango	12
8			Strawberry	11
Sheet1

 

[Scott Huish]

And here it is in VBA:

Sub test()
With Range("A2:B" & Range("C" & Rows.Count).End(xlUp).Row)
    .Columns(1).Formula = "=IFERROR(VLOOKUP(C2,I:J,2,0),"""")"
    .Columns(2).Formula = "=IFERROR(INDEX(H:H,MATCH(C2,I:I,0)),"""")"
    .Value = .Value
End With
End Sub

 

[JoeMo]

Here's an alternative to Scott's VBA solution that doesn't place formulas on the worksheet:

Sub FruitSearch()
Dim lR1 As Long, vA As Variant, R1 As Range, n As Long
Dim lR2 As Long, i As Long, R2 As Range
lR1 = Range("C" & Rows.Count).End(xlUp).Row
lR2 = Range("I" & Rows.Count).End(xlUp).Row
Set R1 = Range("A2:C" & lR1)
Set R2 = Range("I2:I" & lR2)
vA = R1.Value
For i = LBound(vA, 1) To UBound(vA, 1)
    If Not IsError(Application.Match(vA(i, 3), R2, 0)) Then
        n = WorksheetFunction.Match(vA(i, 3), R2, 0)
        vA(i, 1) = R2.Cells(n, 1).Offset(0, 1).Value
        vA(i, 2) = R2.Cells(n, 1).Offset(0, -1).Value
    End If
    On Error GoTo 0
Next i
Range("A2:C" & lR1).Value = vA
End Sub

 

[jadox]

Thank you very much guys. I will test the codes today.
The C and D columns are updated automatically with VBA from another sheet, so putting code into cells is not the best idea, that's why I need a macro to do this.

 

[Scott Huish]

JoeMo:

How is that better? Usually when you put formulas on the sheet and then convert it to the result, it is faster than looping.

 

[jadox]

I've tried JoeMo's code and is not working. Is not doing anything, no error, nothing. I can't figure out what's wrong. Any ideas?
Thanks.

 

[JoeMo]

JoeMo:

How is that better? Usually when you put formulas on the sheet and then convert it to the result, it is faster than looping.

I didn't say it was better, just an alternative.

 

[JoeMo]

I've tried JoeMo's code and is not working. Is not doing anything, no error, nothing. I can't figure out what's wrong. Any ideas?
Thanks.

Works for me using the data and layout you posted. Is your data sheet active when you run the code? How did you install the code (standard module or worksheet module)?

 

[jadox]

Scott, your code works beautifully. Thanks a bunch!
However, the values from columns H, I and J are on another sheet (I used them in the same one for the example) and also the length of columns A, B, C and D can vary, so... can I generalize this somehow?
Thanks!