Find and Replace from table in 1 workbook to another workbook

17 Views Asked by At

New here! I have one workbook ("FormatTest") in which there is a table containing values to find and values to replace. The find column starts at E3, replace column starts at F3. This table could grow. There is another workbook (referred to as FileName) in which I need to run the find/replace on the entire first sheet. I've tried setting my find/replace columns as ranges but should I do an array instead?

I previously used this: Range(Cells.Address).Replace What:=" €“", Replacement:="-" to replace values but I have been asked to use a table instead of adding a line of code for each value since the values to find/replace could grow. 'Define workbooks Set wb1 = Workbooks("FormatTest.xlsm") Set wb2 = Workbooks(FileName) Thank you!

EDIT: Could something like this work?

Sub abbrev() Dim abvtab() As Variant Dim wb1 As Workbook Dim wb2 As Workbook Dim lt As Range Dim FileName As String Dim PathName As String Dim FullFileName As String

PathName = "user path will be here"
FileName = Dir(PathName & "*.*")
FullFileName = PathName & FileName

        Set wb1 = Workbooks("FormatTest.xlsm")
        Set wb2 = Workbooks(FileName)

    With wb1
        Set lt = Range("E3").End(xlDown)

    End With
    
        abvtab = lt

        For i = 1 To UBound(abvtab)
            wb2.Cells.Replace What:=abvtab(i, 1), Replacement:=abvtab(i, 2), LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
        Next i

End Sub

0

There are 0 best solutions below