VBA to replace text in a single column

  • Hi all,

    I'm trying to write a macro to replace string values in a single column. Basically each month I receive an excel file with more than 50 000 rows and I have to replace the text in one of the columns in order to match them to another file.

    The issue is that there are more than 1 000 unique string values, so I've found this syntax, which lets me put all the old and new values in a table and have the macro feed from it:
    [vba]Sub Multi_FindReplace()

    'PURPOSE: Find & Replace a list of text/values throughout entire workbook from a table
    'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

    Dim sht As Worksheet
    Dim fndList AsInteger
    Dim rplcList AsInteger
    Dim tbl As ListObject
    Dim myArray AsVariant

    'Create variable to point to your table
    Set tbl = Worksheets("Sheet1").ListObjects("Table1")

    'Create an Array out of the Table's Data
    Set TempArray = tbl.DataBodyRange
    myArray = Application.Transpose(TempArray)

    'Designate Columns for Find/Replace data
    fndList = 1
    rplcList = 2

    'Loop through each item in Array lists
    For x = LBound(myArray, 1) ToUBound(myArray, 2)
    'Loop through each worksheet in ActiveWorkbook (skip sheet with table in it)
    ForEach sht In ActiveWorkbook.Worksheets
    If sht.Name <> tbl.Parent.Name Then
    sht.Cells.Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
    Next sht
    Next x

    End Sub[/vba]

    The only problem is that it is currently searching through all columns from all sheets which slows it down incredibly. So I'm looking for help to modify it so that it feeds from a table in 1 sheet and replaces the values in a specific column in another (or the same one, doesnt matter).

    All help is much appreciated. I have no experience with VBA, and am using Office 2016 if that matters.

  • Please use code tags in future as per forum rules. Your code appears to be replacing values in every sheet except the one containing the table. Perhaps
    [vba]If sht.Name <> tbl.Parent.Name Then[/vba]needs to be replaced by
    If sht.Name ="relevant sheet name" Then[/vba]?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!