Match Case Search & Replace

[rjb1]

Hi, I am hoping that someone can help me.

I need a macro that does a search and replace in a string but matches the case of the object being replaced.

If my "Find what" is bob dave

and my "Replace with" is sam peter

the macro would find the following in a string:
Bob Dave and replace with Sam Peter
BOB DAVE and replace with SAM PETER
bob dave and replace with sam peter

Thanks!

 

[jbeaucaire]

This macro will prompt you for Search and Replace strings, then replace them in the active sheet with case sensitivity.

Option Explicit
Option Compare Binary

Sub SearchReplaceMatchCase()
'Jerry Beaucaire,  MrExcel.Com
'3/13/2011 - search/replace with Upper, Lower, and Proper case sensitivity
Dim MySrch As String
Dim MyRepl As String
Dim SrchFIND As Range
Dim SrchFRST As Range
Dim MyCount As Long

MySrch = Application.InputBox("String to find", "Search", "Bob Dave", Type:=2)
If MySrch = "False" Then Exit Sub
MyRepl = Application.InputBox("String to replace with", "Replace", "Sam Peter", Type:=2)
If MyRepl = "False" Then Exit Sub

MyCount = 1
On Error Resume Next
MySrch = UCase(MySrch)

Do
    Set SrchFIND = Cells.Find(MySrch, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=True)
    
    If Not SrchFIND Is Nothing Then
        Set SrchFRST = SrchFIND
        Do
            Select Case MyCount
                Case 1  'upper case matches
                    SrchFIND = Replace(SrchFIND, MySrch, UCase(MyRepl))
                Case 2  'lower case matches
                    SrchFIND = Replace(SrchFIND, MySrch, LCase(MyRepl))
                Case 3  'proper case matches
                    SrchFIND = Replace(SrchFIND, MySrch, Application.WorksheetFunction.Proper(MyRepl))
            End Select
            
            Set SrchFIND = Cells.FindNext(SrchFIND)
        
        Loop Until SrchFIND.Address = SrchFRST.Address
        Set SrchFRST = Nothing
        Set SrchFIND = Nothing
        MyCount = MyCount + 1
    End If
    If MyCount = 2 Then MySrch = LCase(MySrch) Else _
        MySrch = Application.WorksheetFunction.Proper(MySrch)
Loop Until MyCount > 3

End Sub

 

[rjb1]

Thanks Jerry!

Your macro works great and is exactly what I wanted.

 

[rjb1]

Hi Jerry - Is it possible to modify the code to only search and replace in a selection?

 

[jbeaucaire]

Try editing these two lines of code, where the search is actually done:

Set SrchFIND = Selection.Find(MySrch, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=True)
    
    If Not SrchFIND Is Nothing Then
        Set SrchFRST = SrchFIND
        Do
            Select Case MyCount
                Case 1  'upper case matches
                    SrchFIND = Replace(SrchFIND, MySrch, UCase(MyRepl))
                Case 2  'lower case matches
                    SrchFIND = Replace(SrchFIND, MySrch, LCase(MyRepl))
                Case 3  'proper case matches
                    SrchFIND = Replace(SrchFIND, MySrch, Application.WorksheetFunction.Proper(MyRepl))
            End Select
            
            Set SrchFIND = Selection.FindNext(SrchFIND)

[/code]