VBA Find and Replace (whole workbook)

[c0087]

A	B
A Anderson	Alex Anderson
B Smith	Brandon Smith

<tbody>
</tbody>

- I pasted my list of names in sheet 1.
- I need to FiIND A1 (entire workbook) and replace all instances with B1.
- then same thing for A2 to B2 and so on so forth.

Thanks in advance

 

[Michael M]

Is that EVERY sheet in the workbook, or might there be exeptions ?

 

[c0087]

Is that EVERY sheet in the workbook, or might there be exeptions ?

YES i need it to search the entire workbook for each individual search. NO there won't be any exceptions




*The process i would be doing manually is
typing A1 in the FIND
typing B1 in the REPLACE
selecting FIND ALL
selecting REPLACE ALL

Within : Workbook
Search by: Columns
Search in: Forumulas

Match case is selected

 

[Domenic]

The following code assumes that the workbook of interest is the active workbook, and it searches all sheets except Sheet1. If you want Sheet1 to be searched as well, the code will need to be amended accordingly. Also, it matches the partial cell contents. To match the entire cell contents instead, replace xlPart with xlWhole.

Sub WorkbookFindAndReplace()

    Dim sourceWorksheet As Worksheet
    Dim currentWorksheet As Worksheet
    Dim findAndReplaceRange As Range
    Dim searchFor As String
    Dim replaceWith As String
    Dim lastRow As Long
    Dim i As Long
    
    On Error Resume Next
    Set sourceWorksheet = ActiveWorkbook.Worksheets("Sheet1")
    If sourceWorksheet Is Nothing Then
        MsgBox "The source worksheet was not found!", vbExclamation
        Exit Sub
    End If
    On Error GoTo 0
    
    With sourceWorksheet
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set findAndReplaceRange = .Range("A1:B" & lastRow)
    End With
    
    For i = 1 To findAndReplaceRange.Rows.Count
        searchFor = findAndReplaceRange(i, 1).Value
        If Len(searchFor) > 0 Then
            replaceWith = findAndReplaceRange(i, 2).Value
            For Each currentWorksheet In ActiveWorkbook.Worksheets
                If currentWorksheet.Name <> sourceWorksheet.Name Then
                    WorksheetFindAndReplace currentWorksheet, searchFor, replaceWith
                End If
            Next currentWorksheet
        End If
    Next i
    
End Sub


Sub WorksheetFindAndReplace(ByVal currentWorksheet As Worksheet, ByVal searchFor As String, ByVal replaceWith As String)

    Dim foundCell As Range

    With currentWorksheet.Cells
        Set foundCell = .Find(What:=searchFor, LookIn:=xlFormulas, LookAt:=xlPart, SearchDirection:=xlNext, MatchCase:=True)
        If Not foundCell Is Nothing Then
            Do
                foundCell.Value = replaceWith
                Set foundCell = .FindNext(foundCell)
            Loop Until foundCell Is Nothing
        End If
    End With
    
End Sub

Hope this helps!

 

[c0087]

The following code assumes that the workbook of interest is the active workbook, and it searches all sheets except Sheet1. If you want Sheet1 to be searched as well, the code will need to be amended accordingly. Also, it matches the partial cell contents. To match the entire cell contents instead, replace xlPart with xlWhole.

Sub WorkbookFindAndReplace()

    Dim sourceWorksheet As Worksheet
    Dim currentWorksheet As Worksheet
    Dim findAndReplaceRange As Range
    Dim searchFor As String
    Dim replaceWith As String
    Dim lastRow As Long
    Dim i As Long
    
    On Error Resume Next
    Set sourceWorksheet = ActiveWorkbook.Worksheets("Sheet1")
    If sourceWorksheet Is Nothing Then
        MsgBox "The source worksheet was not found!", vbExclamation
        Exit Sub
    End If
    On Error GoTo 0
    
    With sourceWorksheet
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set findAndReplaceRange = .Range("A1:B" & lastRow)
    End With
    
    For i = 1 To findAndReplaceRange.Rows.Count
        searchFor = findAndReplaceRange(i, 1).Value
        If Len(searchFor) > 0 Then
            replaceWith = findAndReplaceRange(i, 2).Value
            For Each currentWorksheet In ActiveWorkbook.Worksheets
                If currentWorksheet.Name <> sourceWorksheet.Name Then
                    WorksheetFindAndReplace currentWorksheet, searchFor, replaceWith
                End If
            Next currentWorksheet
        End If
    Next i
    
End Sub


Sub WorksheetFindAndReplace(ByVal currentWorksheet As Worksheet, ByVal searchFor As String, ByVal replaceWith As String)

    Dim foundCell As Range

    With currentWorksheet.Cells
        Set foundCell = .Find(What:=searchFor, LookIn:=xlFormulas, LookAt:=xlPart, SearchDirection:=xlNext, MatchCase:=True)
        If Not foundCell Is Nothing Then
            Do
                foundCell.Value = replaceWith
                Set foundCell = .FindNext(foundCell)
            Loop Until foundCell Is Nothing
        End If
    End With
    
End Sub

Hope this helps!

Will give it a go and report back. Thanks

 

[Michael M]

Could also try....

Sub MM1()
Dim ws As Worksheet, r As Long, lr As Long, sh As Worksheet
Set sh = Sheets("Sheet1")
lr = sh.Cells(Rows.Count, "A").End(xlUp).Row
    For Each ws In Worksheets
        If ws.Name <> "Sheet1" Then
            ws.Activate
                For r = 1 To lr
                    ActiveSheet.UsedRange.Replace sh.Range("A" & r).Value, sh.Range("B" & r).Value, xlWhole, , False, , False, False
                Next r
        End If
    Next ws
End Sub

 

[c0087]

@Domenic @Michael M

Both work great. Thank you guys