Find/REplace from table questions...

[[email protected]]

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]

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.

[email protected] 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

[[email protected]]

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

Dave Peterson wrote:
> 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.
>
> [email protected] 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