Find/Replace from Table

[Steph]

Hello. Is there a way to automate an Edit/Replace from a table? In column
A I have the values of what to Find, and in column B I have the Replace with
values. I know a workaround is to insert a column and do a lookup, but I
was hoping to dtreamline the process a little. Thanks!

[Barry-Jon]

This works - you can tweak it as meets your own needs. Please let me
know if this is the kind of thing you were looking for.

Sub MultiFindReplace()

Dim rngReplaceWith As Excel.Range
Dim rngSearchArea As Excel.Range
Dim lngRepaceCount As Long

Set rngReplaceWith = GetUserRange("Please select find/replace
values range (two columns)")

If Not rngReplaceWith Is Nothing Then

'basic range size validation - a lot more could be done
If rngReplaceWith.Columns.Count = 2 Then

'now get the area in which to do the find/replace
Set rngSearchArea = GetUserRange("Please select the range
in which to find/replace")

If Not rngSearchArea Is Nothing Then

'do the search and replace
For lngRepaceCount = 1 To rngReplaceWith.Rows.Count

rngSearchArea.Replace
What:=rngReplaceWith.Cells(lngRepaceCount, 1).Value, _

Replacement:=rngReplaceWith.Cells(lngRepaceCount, 2).Value, _
MatchCase:=False, _
ReplaceFormat:=False

Next lngRepaceCount

End If

Else

MsgBox "Invalid find/replace range selected", vbExclamation
+ vbOKOnly

End If

End If

End Sub

Private Function GetUserRange(Prompt As String, Optional Title As
String = "Input") As Excel.Range

On Error GoTo ErrorHandler

Dim retVal As Excel.Range

Set retVal = Application.InputBox(Prompt, Title, , , , , , 8)

ExitProc:
Set GetUserRange = retVal
Exit Function

ErrorHandler:
Set retVal = Nothing
Resume ExitProc

End Function

[Steph]

Thanks Barry-Jon,

When I copied the code in, I got a compile error on the following line:
rngSearchArea.ReplaceWhat:=rngReplaceWith.Cells(lngRepaceCount, 1).Value, _
Replacement:=rngReplaceWith.Cells(lngRepaceCount, 2).Value,

MatchCase:=False, _
ReplaceFormat:=False

Any ideas? Thanks for your help!

"Barry-Jon" <barryjonu...@yahoo.co.uk> wrote in message
news:1147426663.4...@y43g2000cwc.googlegroups.com...

[Barry-Jon]

Most likely spacing/line breaks from the copy / paste. Try putting the
code all on one line without the _ and line breaks.

rngSearchArea.Replace What:=rngReplaceWith.Cells(lngRepaceCount,
1).Value, Replacement:=rngReplaceWith.Cells(lngRepaceCount, 2).Value,
MatchCase:=False, ReplaceFormat:=False

[Steph]

That did it. Thanks! I'll give it a shot!!

"Barry-Jon" <barryjonu...@yahoo.co.uk> wrote in message

news:1147447015.6...@g10g2000cwb.googlegroups.com...