Help!! Not sure how to write this simple code. NEWBIE Find and Replace

[LukeWayne]

I am working on a <acronym title="visual basic for applications">VBA</acronym> to Find from Column B (Sheet 2) and Find in (Sheet 1) and Replace the value = Column B in Sheet 2 of which = Column A (the replacement value.) When I run the macro it works, but I have multiple values = to the same value in Column B and it is only choosing one of those values to replace. How do I write this code to let it replace with multiple values it is equal to.

Here is the code:

Option Explicit
Option Compare Text

Sub FIND_AND_REPLACE()
On Error Resume Next
Application.ScreenUpdating = False

Dim toFind As String, toReplace As String, rng As Range, cel As Range, i As Long, frow As Long, _
frowT As Long, wk As Worksheet, ws As Worksheet, j As Long
Set wk = Sheet1: Set ws = Sheet2

frow = wk.Range("AM" & Rows.Count).End(xlUp).Row
frowT = ws.Range("A" & Rows.Count).End(xlUp).Row

Set rng = wk.Range("AM2:AQ" & frow)

For i = 2 To frowT
toFind = ws.Range("B" & i).Value
toReplace = ws.Range("A" & i).Value
rng.Replace What:=toFind, Replacement:=toReplace, LookAt:=xlWhole, MatchCase:=False
Next i

For i = 2 To frow
wk.Range("AR" & i) = ""

For j = 39 To 43
If Trim(wk.Cells(i, j)) <> "" Then
wk.Range("AR" & i) = wk.Range("AR" & i) & "," & Trim(wk.Cells(i, j))
End If
Next j

If Trim(wk.Range("AR" & i)) <> "" Then
wk.Range("AR" & i) = Right(wk.Range("AR" & i), Len(wk.Range("AR" & i)) - 1)
End If

Next i

Application.ScreenUpdating = True
MsgBox "Done"
End Sub

 

[LukeWayne]

Option Explicit
Option Compare Text

Sub FIND_AND_REPLACE()
On Error Resume Next
Application.ScreenUpdating = False

Dim toFind As String, toReplace As String, rng As Range, cel As Range, i As Long, frow As Long, _
frowT As Long, wk As Worksheet, ws As Worksheet, j As Long
Set wk = Sheet1: Set ws = Sheet2

frow = wk.Range("AM" & Rows.Count).End(xlUp).Row
frowT = ws.Range("A" & Rows.Count).End(xlUp).Row

Set rng = wk.Range("AM2:AQ" & frow)

For i = 2 To frowT
toFind = ws.Range("B" & i).Value
toReplace = ws.Range("A" & i).Value
rng.Replace What:=toFind, Replacement:=toReplace, LookAt:=xlWhole, MatchCase:=False
Next i

For i = 2 To frow
wk.Range("AR" & i) = ""

For j = 39 To 43
If Trim(wk.Cells(i, j)) <> "" Then
wk.Range("AR" & i) = wk.Range("AR" & i) & "," & Trim(wk.Cells(i, j))
End If
Next j

If Trim(wk.Range("AR" & i)) <> "" Then
wk.Range("AR" & i) = Right(wk.Range("AR" & i), Len(wk.Range("AR" & i)) - 1)
End If

Next i

Application.ScreenUpdating = True
MsgBox "Done"
End Sub

 

[LukeWayne]

Please, needing someone's help. Trying to learn, but stuck on what to do next. So if you have any idea I am willing to try or if you need more information.

 

[Tetra201]

Seems to work OK -- replaces all values in range Sheet1!AM2:AQn that mach those listed in Sheet2!B2:Bm.

 

[LukeWayne]

Yes, it does work for this, but my problem is that some values don't show up because the same value in Column B. So essentially it is only choosing one value to replace with from Column A to Sheet 1 Columns. I am attaching a picture to show.

Failed image deleted

 

[LukeWayne]

Seems to work OK -- replaces all values in range Sheet1!AM2:AQn that mach those listed in Sheet2!B2:Bm.

Yes, it does work for this, but my problem is that some values don't show up because the same value in Column B. So essentially it is only choosing one value to replace with from Column A to Sheet 1 Columns. I am attaching a picture to show.

 

[Tetra201]

Yes, it does work for this, but my problem is that some values don't show up because the same value in Column B. So essentially it is only choosing one value to replace with from Column A to Sheet 1 Columns...

And how many values should it choose?

 

[LukeWayne]

And how many values should it choose?

It should choose at least two. Sometimes it is just one. These manufacturer numbers repeat themselves only twice and are equal to a different value and some are only listed once and simply equal one value so easy. It is the others that list twice and equal two different values that is giving me troubles.

 

[Tetra201]

... These manufacturer numbers repeat themselves only twice and are equal to a different value ...

Suppose you have two "3130LF" in Sheet2!B:B. How many "3130LF" can there be in Sheet1!AM:AQ?

 

[LukeWayne]

Suppose you have two "3130LF" in Sheet2!B:B. How many "3130LF" can there be in Sheet1!AM:AQ?

There is only one. Sheet 1 has one "3130LF" and is = 64.946 & 10.494 from Sheet 2. And by the way I am only working with one column in Sheet 1 (this VBA code is something I have used in the past and am trying to modify for this purpose). In theory if "3130LF" = the numbers above, then maybe have the macro run in a way that would replace "3130LF" with 64.946, 10.494 or put both in two separate columns if that would be easier.