Hello VBA experts,
Its been a long while since i tried using VBA and I'm running in to some problems. Im columns A-E in a spreadsheet and attempting the following:
Columns A,B & D are populated with data. I need to:
1. loop through the values in column D
2. Compare each value in column D to all of the values in column A
3. If there is a match, copy cell B which corresponds to the matching value in column A
4. Paste that value in column E next to the corresponding matching value in column D
If thats not too confusing...
So as an example:
A_B_C_D_E
a_1___r
g_2___a_1
e_3___b
d_4___c
t_5___n
In column D, a match for "a" is found in column A, row 1, and the value in column A, row 2 is pasted to the matching "a" in Column E.
All ranges would need to be dynamic.
I've tried piecing this together from examples i've found with not luck so far. Below is what i've been working with. I know its incorrect, and results in copying all of the values in column A to column E.
I've attached the spreadsheet as well.Sub compareAndCopy() Dim lastRowA As Long Dim lastRowD As Long Dim lastRowE As Long Dim i As Integer Dim j As Integer Application.ScreenUpdating = False lastRowD = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "D").End(xlUp).Row lastRowA = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "A").End(xlUp).Row lastRowE = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "E").End(xlUp).Row For i = 1 To lastRowD For j = 1 To lastRowA If Sheets("Sheet1").Cells(i, 1).Value = Sheets("Sheet1").Cells(j, 1).Value Then Sheets("Sheet1").Cells(i, 1).Offset(, 1).Copy Destination:=Sheets("Sheet1").Cells(i, 1).Offset(, 4) Exit For Exit For End If Next j Next i Application.ScreenUpdating = True End Sub
Any help would be greatly appreciated!
Thanks,
ChrisRowMatchTest.xlsm