It's stopped working

I have a code... and I am trying to use it with a different file.

The files is set up the same, the code has been unedited, but it's not working

Sub findAndReplace()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim searchFor As String
Dim searchCol As Range

Set ws1 = Sheets("SBK") 'sheet for which we look in column A
Set ws2 = Sheets("Sheet1") 'sheet we try to match with column E

'last row on the sheet with the updated information
lastRow = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
'column E on sheet we are going to update
Set searchCol = ws2.Columns(5)

'work with one row at a time; i is the row number
For i = 1 To lastRow
'read the entry in column A for this row
searchFor = ws1.Range("A" & i)
'attempt to find a match
matchRow = 0
On Error Resume Next
matchRow = Application.WorksheetFunction.Match(searchFor, searchCol, 0)
If matchRow > 0 Then
'if we got here, we found a match
ws2.Range("D" & matchRow) = ws1.Range("D" & i)
ws2.Range("E" & matchRow) = ws1.Range("E" & i)
ws2.Range("F" & matchRow) = ws1.Range("F" & i)
ws2.Range("G" & matchRow) = ws1.Range("G" & i)
End If
Next i

End Sub


What it should do:
I want it to look in SBK at column A and match it to column E of Sheet1. Then copy collumns DEFG from SBK to DEFG of sheet1.... Is there some reason it's not doing it?

Are you getting an error message? Or is simply nothing happening?

How do you run the macro? Hot-key? Button? Macros dialog?

Note: Someone on here made it for me, and I was so greatful. It worked the once, but after that it does nothing.

No, my mouse is a hourglass for about three minutes then when it goes off nothing...

I start it running by hitting alt+f8 then run.

Yes, it looks familiar ...

Are you getting an error message? Or is simply nothing happening?

How do you run the macro? Hot-key? Button? Macros dialog?

Would it be possible for you to upload the workbook?

If it freezes for that long, I think there must be many thousands of rows of data?

It should match two numbers and then I want it to paste a text and some numbers.

I am not very good with this program so the last bit of this confuses me. Do you mean it all needs to be numeric?

Originally Posted by MSP77079

Would it be possible for you to upload the workbook?

If it freezes for that long, I think there must be many thousands of rows of data?

I can't upload it... The company I am doing this for said I can seek help but I can't give anyone the actual thing...

By the way there are 29827 rows of data....

Do you mean it all needs to be numeric?

I think Bryan meant the opposite. It should NOT be numeric.

Is the match you are looking for an EXACT match? In other words, when searching for "ABC", would "ABCD" be considered a match, or not a match?

What format should it be in? I know how to format cells if nothing else... I actually know a lot aout excel... except the programing...

And the first time I used the code I put up there, I had almost double that mant rows.
and it should be an exact match. There are no comments or anything on the cells either. So that won't hurt it.

If you have numbers, try formatting as text.

Ok, testing it... be back in four or so minutes

I think you are going to have to "walk" through the code and find out where the problem is.

I would put a break-point in a the line:

Please Login or Register  to view this content.

See that "searchFor" is what you expected. Do you have a row where you KNOW there should be a match found? If so, you can put in code like this:

Please Login or Register  to view this content.

(where 1234 is the row number where you know there should be a match) and but a breakpoint on the MsgBox ... then, the code will run down to that row before hitting the breakpoint.

I got the message that it can't run in breakpoint mode... I don't know what that means but I bet it is important.

5 rows I can do... I'll upload it to you send it, and give the link directly to the people that need it. I don't want just anyone downloading it.

Sorry for having a stick up my *** and being so picky about sending it around. I sent the small file/link to MSP77079

Oh sorry I forgot to say, if there is no match it should be left blank. I really appreciate your all's help because without it I would be copying and pasting 28000 things by hand.

Got it. There's no code in it, though. I'll take the code you posted earlier today.

Originally Posted by MSP77079

Got it. There's no code in it, though. I'll take the code you posted earlier today.

I put the code on there... Do you have to save it a special way? Because if you do, I don't know how to do it... I saved it in notepad after using it. xD

Did not need the code. Problem is that on SBK there is:
01013
01020

On Sheet1 there is:
1013
1020

These do not match.

but it doesn't do anything even on exact matches... The numbers get bigger in the end and all but 8000 would be exact... Those were some of the early ones. I am sure that helps though. its like

0xxxx
0xxxx
0xxxx
0xxxx
1xxxx
1xxxx

and they keep getting bigger, in the end it matches up


I will go through and fix those.

OK ... but, I still think the essence of the problem is numbers vs. text. The leading 0 being dropped is a good indication of that.

So, I'll work on that issue. Just wanted to confirm.

Yep. that was the problem. Try this:

Please Login or Register  to view this content.

the only problem with this code is that it will match
01234 with 1234.

If all of the stuff in column A / E is the same length then, that's OK. If not, I should do another test. I.e., if it is numeric, then it has to satisfy 2 criteria:
1. the match works
2. the cell entries are the same length

Hmmm. good idea. I'll do that now.

OK ... so, this works, and you need to decide if you want to use it or not.

If you want 01013 to match with 1013, then use the code I posted a few minutes ago. If you DO NOT want 01013 to match with 1013 then add the modification below:

Please Login or Register  to view this content.

Thanks for the feedback. I'll check in the morning.

It worked! Thank you very much!!! <3<3<3<3

I couldn't reply in the morning, I had to go to school

Thanks for the feedback. You have your priorities right.