Find and replace using array - need to alter it

[lYoung]

'this is not my code, however I would like to use it but I am not searching multiple sheets only 1. How can I tweak the code but use it on only one sheet? I've tried many variations with no luck - help would be greatly appreciated

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

 

[igold]

Hi lYoung,

Welcome to the MrExcel forum.

If you could tell us more about your data, such as where it is in your workbook what data do you want to compare and replace and what the criteria for replacement is. Where you want the results, is your data in range or a table (table name) and things like that, a code using an array can be easily written.

Just looking at the code you quoted tells us nothing about your needs.

 

[lYoung]

absolutely, thank you for the reply
I am trying to do a combination of a vlookup and find/replace between two sheets.
First sheet is a range, rows will vary but columns are set, and the range will always change. This sheet is contains costs for construction jobs, i.e type of costs, vendors, cost descriptions.
The second sheet has a table consisting of only two columns, this table is set, will not change. I'm searching the "Second" sheet, first column cells for the text in the "First" sheet, column "W" and replacing text in the "First" sheet, column "W", with text in 2nd column if text matches.

COLUMN WITH JOB COST DESCRIPTION: COLUMN "W"

2ND SHEET WITH TABLE WITH COST CODE DESCRIPTION AND COST CODE

Please let me know if I did not answer your question, this is my first time reaching out to a forum for assistance.

thank you

 

[Fluff]

How about

Sub iYoung()
   Dim Ary As Variant
   Dim i As Long

   Ary = Worksheets("Sheet1").ListObjects("Table1").DataBodyRange.Value
   For i = 1 To UBound(Ary)
     Sheets("Sheet2").Range("W:W").Replace Ary(i, 1), Ary(i, 2), xlPart, , False, , False, False
   Next i
End Sub

Change sheet names to suit

 

[lYoung]

How about

Sub iYoung()
   Dim Ary As Variant
   Dim i As Long

   Ary = Worksheets("Sheet1").ListObjects("Table1").DataBodyRange.Value
   For i = 1 To UBound(Ary)
     Sheets("Sheet2").Range("W:W").Replace Ary(i, 1), Ary(i, 2), xlPart, , False, , False, False
   Next i
End Sub

Change sheet names to suit

Rich,
I tried running the code and received this error.

your code referenced different sheets. I do not see my type-o in the syntax

 

[Fluff]

You're missing the " from the end of the sheet name.

 

[lYoung]

Made the correction, have a run time error, on

 

[Fluff]

Do you have a sheet called Sheet4 (as seen on the tab) & a table called Table1 on that sheet?

 

[lYoung]

I was referencing the Sheet so I renamed the Sheet to the Sheet name. The code ran without errors, but replacement did not happen, i.e. APPLIANCES replaced with APP

 

[Fluff]

Can you post some sample data of both sheets using the XL2BB add-in?