Find/REplace from table questions...

[deforsythe]

I found most of the answers I needed in this post:
http://groups.google.com/group/micr...ace+range+excel&rnum=1&hl=en#37219aa2adb07dd7

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]

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.

 

[deforsythe]

Excellent! Thank you for your input. I'm going to give all of that a
try.

I haven't progressed much beyond the cutting and pasting of other
people's macros, but I've got a book and I'm working on it..... Thanks

Dave