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