Dmitry
10-09-2017, 11:50 AM
I'm looking for VBA code to run in Excel to find and replace lots of words.
Basically, it will be a simple Excel file where Sheet1 contains phrases in 1 column which contains names to be replaced (not the whole phrase but one name which might consist of few words). Second sheet2 contains in 1 column values which I need to find in Sheet1 (there might be more than one time when value appears to be found in 1st column) and column which contains translation. I don't need Google API for this because names are very custom.
Dim sht As WorksheetI came across to the following script but it says: run-time error '9'. Subscript out of range. Please advise.
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
Basically, it will be a simple Excel file where Sheet1 contains phrases in 1 column which contains names to be replaced (not the whole phrase but one name which might consist of few words). Second sheet2 contains in 1 column values which I need to find in Sheet1 (there might be more than one time when value appears to be found in 1st column) and column which contains translation. I don't need Google API for this because names are very custom.
Dim sht As WorksheetI came across to the following script but it says: run-time error '9'. Subscript out of range. Please advise.
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