I need to search the 2nd column of the DATA tab and see if it matches the first column of the INDEX tab. If so I need to replace the 2nd column in the DATA tab with the value in the second column in the INDEX tab and then scroll through all rows of the DATA tab and do the same thing
example.
If Albert b, def in the DATA tab matches def, 2 in the INDEX tab then replace def in the DATA tab with 2
I have the following code so far but it doesnt seem to work
For i = 1 To 1000
For j = 2 To 1000
If Sheets("DATA").Cells(i, 2).Value = Sheets("INDEX").Cells(j, 1).Value Then
Range(Sheets("INDEX").Cells(j, 2)).Copy
Range(Sheets("DATA").Cells(i, 2)).Paste
Exit For
End If
Next j
DATA
[TABLE="width: 500"]
<tbody>[TR]
[TD]Alan[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD]Alan[/TD]
[TD]def[/TD]
[/TR]
[TR]
[TD]Alan[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alan[/TD]
[TD]wer[/TD]
[/TR]
[TR]
[TD]Albert b[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD]Albert b[/TD]
[TD]def[/TD]
[/TR]
[TR]
[TD]Albert b[/TD]
[TD]wer[/TD]
[/TR]
[TR]
[TD]Phillips[/TD]
[TD]rtt[/TD]
[/TR]
[TR]
[TD]Phillips[/TD]
[TD]wer[/TD]
[/TR]
[TR]
[TD]Phillips[/TD]
[TD]mnb[/TD]
[/TR]
</tbody>[/TABLE]
INDEX
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]abc[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]asd[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]wer[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]rtt[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]asdasd[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]mnb[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]sdfs[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]def[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]tyfyut[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sdfsdf[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]
example.
If Albert b, def in the DATA tab matches def, 2 in the INDEX tab then replace def in the DATA tab with 2
I have the following code so far but it doesnt seem to work
For i = 1 To 1000
For j = 2 To 1000
If Sheets("DATA").Cells(i, 2).Value = Sheets("INDEX").Cells(j, 1).Value Then
Range(Sheets("INDEX").Cells(j, 2)).Copy
Range(Sheets("DATA").Cells(i, 2)).Paste
Exit For
End If
Next j
DATA
[TABLE="width: 500"]
<tbody>[TR]
[TD]Alan[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD]Alan[/TD]
[TD]def[/TD]
[/TR]
[TR]
[TD]Alan[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alan[/TD]
[TD]wer[/TD]
[/TR]
[TR]
[TD]Albert b[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD]Albert b[/TD]
[TD]def[/TD]
[/TR]
[TR]
[TD]Albert b[/TD]
[TD]wer[/TD]
[/TR]
[TR]
[TD]Phillips[/TD]
[TD]rtt[/TD]
[/TR]
[TR]
[TD]Phillips[/TD]
[TD]wer[/TD]
[/TR]
[TR]
[TD]Phillips[/TD]
[TD]mnb[/TD]
[/TR]
</tbody>[/TABLE]
INDEX
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]abc[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]asd[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]wer[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]rtt[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]asdasd[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]mnb[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]sdfs[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]def[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]tyfyut[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sdfsdf[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]