How to create a loop of the Find & Replace Function

Hi,

I already have a code for the loop but is no working properly.

I attached a test Worksheet for you to test the code.

Test.xlsx

The idea of the code is not to do a Find & Replace for the 369 fields.

So the code must read the Row B on Sheet 2 and look it up in the Row A on Sheet 1, then when its finds the number in the row it must replace it with the number in row A on Sheet 2.

Example: First step will be get the NUmber from Row B on Sheet 2 (115000059001000) and look for it in the Row A on SHeet 1. In this case the number does not repeats but is very problable that many others will. So when it finds the number then it should replace it with (100-100). Whty with this spesific number? Because is the number next to him in the Field 2.


Here is the code that I have so far.

Option Explicit
Sub ReplaceOld()
Dim lastOld, lastOld2, nxtRow
Dim oldNum As String
Dim c
'Determine Last Row of Old Number List
lastOld = Sheets(2).Range("B" & Rows.Count).End(xlUp).Row

'Determine Last Row of "Random and Old Number" List
lastOld2 = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row

'Loop through Old Numbers in Column B (2)
For nxtRow = 2 To lastOld
oldNum = Sheets(2).Range("B" & nxtRow)

'Find each Old number in "Random and Old Number" list
With Sheets(2).Range("B2:B" & lastOld2)
Set c = .Find(oldNum, lookat:=xlWhole)
If Not c Is Nothing Then

'When found, replace it with New number from Column A (1)
Sheets(1).Range(c.Address) = Sheets(2).Range("A" & nxtRow)

End If
End With
Next
End Sub

I got this code form some forum post and I edit it. But It is not working as I intend
Hope you understand.

Anticipated thanks