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

 

[lYoung]

here is the complete workbook
2.29.20_MrExcel.xlsm

 

[Fluff]

Not sure if you saw post#30?

 

[lYoung]

yes, you are correct column U.
Now that you have the sheet, a bit more information, the data on the sheet is coming from 2 sources, reason for the differences in the formats. We pull it from a construction restoration software, (RM), however the data we are pulling is a combination of data synced from QuickBooks and the data we have in RM. My objective is to make data, cost codes in column U, i.e. APP:MATERIALS, consistent, remove cost type, but need it for now for column U. Replace Lbr. Burden and Market Cond. in column W, with Labor.
Need to consolidate (group) rows by Source2, column AB, Payee, column X, and Cost Code, column U, Second grouping is by Estimate, column AA, and then cost code. I need to have a report that consolidates costs. I'm able to do the grouping in Power Query, but would be awesome if I could do it in VBA. thank you

 

[Fluff]

As that is a totally different question, you will need to start a new thread.
Do you have the code working for replacing the contents of col U

 

[lYoung]

Received an error 9 on this line of code.Ary = Worksheets("Sheet4").ListObjects("Table1").DataBodyRange.Value
I changed "Table" to "Table1", same error either way

Public Sub replaceCCs()

Dim Ary As Variant
Dim i As Long

'this is the sheet, sheet 4, contains the table, cost code description(column1) and costcode(column2)
Ary = Worksheets("Sheet4").ListObjects("Table1").DataBodyRange.Value
For i = 1 To UBound(Ary)
Sheets("Sheet3").Range("u:u").replace Ary(i, 1), Ary(i, 2), xlPart, , False, , False, False

Next i

 

[Fluff]

You need to use the actual sheet names like

Public Sub replaceCCs()
   
   Dim Ary As Variant
   Dim i As Long
   
   'this is the sheet, sheet 4, contains the table, cost code description(column1) and costcode(column2)
   Ary = Sheets("CodeDESC").ListObjects("Table1").DataBodyRange.Value
   For i = 1 To UBound(Ary)
      Sheets("JobCostDetailByCostCode (2)").Range("u:u").Replace Ary(i, 1), Ary(i, 2), xlPart, , False, , False, False
   Next i
End Sub

 

[lYoung]

I was just changing that now. Why/how can you only use UBound and not LBound in this code?

 

[Fluff]

You can use LBound, but when you create an array like that the Lbound will always be 1

 

[lYoung]

So you do not need to use both LBound and UBound together?
The code worked, thank you. Just want to make sure i understand LBound and UBound. I thought I had to use both.

 

[Fluff]

So you do not need to use both LBound and UBound together?

No you don't but there is nothing to prevent you from doing so.
In some respects it's safer to use them both