Help With Find & Copy Code

[Dazzawm]

I was given the code below that looks at column A on sheet 1 and then looks for that value in column 28 on sheet 2. When a match is found it copies what is in column B on sheet 1 into column 29 on sheet 2.

I have 2 problems, the first is that when it finds a match it adds the data next to it fine but when that number occurs later down in the Sheet it doesnt do anything. Also it takes forever to run and sometimes stops responding as it has to look at 30-40,000 rows. can anyone help please.

Thanks.

Sub MatchAndCopy1()
Sheets(1).Activate
a = 1
Do While Cells(a, 1) <> ""
x = Cells(a, 1).Text
B = 1
Do While Sheets(2).Cells(B, 28) <> ""
If x = Sheets(2).Cells(B, 28).Text Then
Sheets(2).Cells(B, 29) = Cells(a, 2).Text
GoTo nxt
End If
B = B + 1
Loop
nxt:
a = a + 1
Loop
Sheets(2).Activate
End Sub

 

[Weaver]

This sounds like you need to put more focus on sheet 2.

Couldn't you use a vlookup formula in column 29 on sheet 2 to achieve this?

in row 2:

=vlookup(AB2,sheet1!A:B,2,0)

and copy down

You might have to change the name of the sheet to fit your setup.

To clarify

I have 2 problems, the first is that when it finds a match it adds the data next to it fine but when that number occurs later down in the Sheet it doesnt do anything.

which dataset are we talking about here, sheet1, or sheet2?