PDA

View Full Version : VBA to replace text in a single column



reactionhank
04-10-2018, 11:33 PM
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:


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 As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant

'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) To UBound(myArray, 2)
'Loop through each worksheet in ActiveWorkbook (skip sheet with table in it)
For Each 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

End If
Next sht
Next x
End Sub

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.

Bob Phillips
04-11-2018, 02:50 AM
Is this what you want?


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 cell As Range
Dim tbl As ListObject
'Designate Columns for Find/Replace data
Const fndList As Long = 1
Const rplcList = 2

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

tbl.DataBodyRange.Replace What:=fndList, _
Replacement:=rplcList, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End Sub