Function to Find & Replace within Range

[thansen]

How do I find & replace text within a range using a function. I can do
this with the Replace command on the Edit menue by selecting a range
and filling in the FINE and REPLACE fields then select REPLACE ALL.

I want to search the TEXT range (D23032) for the "old_text" given in
Cell F3 and replace any matches with the "new_text given in cell G3.

Then I want to repeat this process with "old_text" given in Cell F4 and
replace any matches with the "new_text given in cell G4.

And so on.....until the next cell going down in Column F is blank.

Thanks for any help.

See file attached.


+----------------------------------------------------------------+
| Attachment filename: fitting part number cross reference.xls |
|Download attachment: http://www.excelforum.com/attachment.php?postid=361335|
+----------------------------------------------------------------+

 

[JR]

Try this:
Select your data in column J starting with '318' in row 3
and name it "SearchR"; select data in column B starting
with row 3 and name it "TargetR". Paste in the following
macro and run it. It's not elegant but should do the job.

JR

Sub EPN()
Dim SR As Range
Dim TR As Range
Set SR = Range("SearchR")
Set TR = Range("TargetR")
Dim x As Integer
Dim Sx, Tx, hTx As String

For Each c In SR
Sx = Trim(c.Value)
For Each d In TR
Tx = Trim(d.Value)
htx = Tx
If Len(Tx) >= Len(Sx) Then
Tx = Left(Tx, Len(Sx))
If Sx = Tx Then
d.Offset(0, 2) = Trim(c.Offset(0, 1).Value) _
+ Right(htx, Len(htx) - Len(Tx))
End If
End If
Next d
Next c
End Sub

 

[Dave Peterson]

Another option:

I recorded a macro when I did a simple change and then modified it to loop
through a range of cells:

Option Explicit
Sub testme01()

Dim RngF As Range
Dim myCell As Range
Dim RngToChange As Range

With ActiveSheet
Set RngToChange = .Range("D23032")
Set RngF = .Range("F3", .Cells(.Rows.Count, "F").End(xlUp))

For Each myCell In RngF.Cells
RngToChange.Replace What:=myCell.Value, _
Replacement:=myCell.Offset(0, 1).Value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
Next myCell
End With

End Sub