0

I am looking to make a macro that uses the find and replace function using data in a two column table (Column A and Column B), that replaces everything that contains data from Column A in the rest of workbook to data from the same row in Column B.

I have the code for a macro below that does this, but right now it only changes the data that is in Column C. How difficult is it to edit this macro to change the edited range from Column C to every other sheet in the workbook?

Any help would be appreciated. Thank you.

Here is the current code:

Public Sub MyReplace()
' Define variables '
Dim sh As Worksheet
Dim dst As Range
Dim vals() As Variant
Dim i As Integer
' Reference to a sheet '
Set sh = ThisWorkbook.Worksheets("Sheet1")
' Reference to the range where to replace (column C) '
Set dst = sh.Range(sh.Cells(1, 3), _
                   sh.Cells(sh.UsedRange.Row + sh.UsedRange.Rows.Count - 1, 3))
' Get the replacement values pairs (from columns A and B) '
vals = sh.Range(sh.Cells(1, 1), _
                sh.Cells(sh.UsedRange.Row + sh.UsedRange.Rows.Count - 1, 2)).Value
' process each replacement values pair '
For i = LBound(vals) To UBound(vals)
    ' If both values are not empty '
    If Len(Trim(vals(i, 1))) > 0 And Len(Trim(vals(i, 2))) > 0 Then
        ' then perform the replacement '
        dst.Replace What:=Trim(vals(i, 1)), Replacement:=Trim(vals(i, 2)), _
                    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                    SearchFormat:=False, ReplaceFormat:=False
    End If
Next
End Sub

1 Answer 1

0

Try the following and let me know if that is what you are after (or if it works), it works on my data but I do not know the format you are working with... I think it is what you are after.

Option Explicit

Public Sub MyReplace()
' Define variables '
Dim sh As Worksheet
Dim sh1 As Worksheet: Set sh1 = Sheet1
Dim dst As Range
Dim vals() As Variant
Dim i As Integer

For Each sh In Worksheets
    If sh.Name <> Sheet1.Name Then
        vals = sh.UsedRange.Value
        Set dst = sh.UsedRange
        MsgBox UBound(vals)
        ' process each replacement values pair '
        For i = LBound(vals) To UBound(vals)
            ' If both values are not empty '
            If Len(Trim(sh1.Cells(i, 1))) > 0 And Len(Trim(sh1.Cells(i, 2))) > 0 Then
                ' then perform the replacement '
                dst.Replace What:=Trim(sh1.Cells(i, 1)), replacement:=Trim(sh1.Cells(i, 2)), _
                                LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                                SearchFormat:=False, ReplaceFormat:=False
            End If
        Next i
    End If
Next sh
End Sub

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .