Sometimes, just recording a macro in a test workbook will give you the answer:
I recorded this little bit:
Cells.Replace What:="a", Replacement:="b", LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Notice the LookAt:=xlWhole parm. You'll want to add it to your .replace line of
code.
And you can swap columns by changing this line--in fact, I've added the xlwhole
stuff, too:
rngSearchArea.Replace What:=rngReplaceWith.Cells(lngRepaceCount, 2).Value, _
Replacement:=rngReplaceWith.Cells(lngRepaceCount, 1).Value, _
MatchCase:=False, lookat:=xlWhole, ReplaceFormat:=False
Notice the ", 2" and ", 1" have been swapped. They tell excel which column to
use.
Another option would have been just to swap the "what" and "replacement"
keywords.
rngSearchArea.Replace _
replacement:=rngReplaceWith.Cells(lngRepaceCount, 1).Value, _
what:=rngReplaceWith.Cells(lngRepaceCount, 2).Value, _
MatchCase:=False, lookat:=xlWhole, ReplaceFormat:=False
Now the replacement value comes from column 1 and the "what to replace" value
comes from column 2.
wrote:
I found most of the answers I needed in this post:
http://groups.google.com/group/micro...219aa2adb07dd7
Thanks Barry-Jon for this code:
___________________________
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
________________________________________________
The questions I have:
Question 1:
How can I make this macro do the "Match Entire Cell Contents" that is
available in the Replace menu option?
I tried just adding the condition MatchEntireCellContents:=True but
that wasn't an acceptable condition.
Question 2:
Is there a way to reverse the 2 columns in the Find/Replace range? By
that I mean, can I make it so that Column 2 in the range is the find
value and column 1 is the replace value?
--
Dave Peterson