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]

this is the table, not sure this is what you wanted

Cost Code Drescription	Cost Code
APPLIANCES	APP
CABINETRY	CAB
CLEANING	CLN
CONCRETE & ASPHALT	CNC
CON:SUBCONTRACT	CON
CONT: PACKING,HANDLNG,STORAGE	CON
CONTENT MANIPULATION	CON
CONTENTS	CON
COUNTERTOP	CTP
DEBRIS DISPOSAL	DEBRIS DISPOSAL
GENERAL DEMOLITION	DMO
DOORS	DOR
DRYWALL	DRY
ELECTRICAL	ELE
FLOOR COVERING - CARPET	FCC
FLOOR COVERING - STONE	FCR
FLOOR COVERING STONE	FCR
FLOOR COVERING - CERAMIC TILE	FCT
FLOOR COVERING TILE	FCT
FLOOR COVERING - VINYL	FCV
FLOOR COVERING VINYL	FCV
FLOOR COVERING - WOOD	FCW
PERMITS AND FEES	FEES
FENCING	FEN
FINISH CARPENTRY / TRIMWORK	FNC
FINISH HARDWARE	FNH
FIREPLACE	FPL
FIRE PROTECTION SYSTEM	FPS
FLOOR COVERING RESILIENT	FRC
FRAMING & ROUGH CARPENTRY	FRM
GLASS, GLAZING, & STOREFRONTS	GLS
HOUSEWARES DINING & FLAT	HDF
HAZARDOUS MATERIAL REMEDIATION	HMR
HAZERDOUS MATERIALS & REMEDIATIN	HMR
HEAT, VENT & AIR CONDITIONING	HVC
INSULATION MECHANICAL	INM
INSULATION	INS
LABOR ONLY	LAB
LAWN, GARDEN & PATIO	LGP
LINNENS & SOFTGOODS	LIN
LIGHT FIXTURES	LIT
LANDSCAPING	LND
MASONRY	MAS
MARBLE	MBL
MOISTURE PROTECTION	MPR
MIRRORS & SHOWER DOORS	MSD
MOBILE HOMES, SKIRTING & SETUP	MSK
METAL STRUCTURES & COMPONENTS	MTL
ORNAMENTAL IRON	ORI
INTERIOR LATH & PLASTER	PLA
PLUMBING	PLM
PANNELING & WOOD WALL FINISHES	PNL
PAINTING	PNT
SWIMMING POOLS & SPAS	POL
ROOFING	RFG
SCAFFOLDING	SCF
SIDING	SDG
SOFFIT, FASCIA, & GUTTER	SFG
SPRINKLERS	SPR
STAIRS	ST
STEEL JOIST COMPONENTS	STJ
STEEL COMPONENTS	STL
STUCCO & EXTERIOR PLASTER	STU
SUPERVISION	SUP
TOILET & BATH ACCESSORIES	TBA
TILE	TIL
TIMBER FRAMING	TMB
TEMPORARY REPAIRS	TMP
WINDOWS ALUMINUM	WDA
SLIDING PATIO DOOR	WDP
WINDOW REGLAZING & REPAIR	WDR
WINDOW TREATMENT	WDT
WINDOWS - VINYL	WDV
WINDOWS WOOD	WDW
WALLPAPER	WPR
WATER EXTRACTION & REMEDIATION	WTR
EXTERIOR STRUCTURES	XST

 

[lYoung]

this is the "w:w" range I am searching and want to replace with text in table, 2nd column, if text matches

JCReport_v1.xlsm
	D
1	Cost Code Drescription
2	APPLIANCES
3	CABINETRY
4	CLEANING
5	CONCRETE & ASPHALT
6	CON:SUBCONTRACT
7	CONT: PACKING,HANDLNG,STORAGE
8	CONTENT MANIPULATION
9	CONTENTS
10	COUNTERTOP
11	DEBRIS DISPOSAL
12	GENERAL DEMOLITION
13	DOORS
14	DRYWALL
15	ELECTRICAL
16	FLOOR COVERING - CARPET
17	FLOOR COVERING - STONE
18	FLOOR COVERING STONE
19	FLOOR COVERING - CERAMIC TILE
20	FLOOR COVERING TILE
21	FLOOR COVERING - VINYL
22	FLOOR COVERING VINYL
23	FLOOR COVERING - WOOD
24	PERMITS AND FEES
25	FENCING
26	FINISH CARPENTRY / TRIMWORK
27	FINISH HARDWARE
28	FIREPLACE
29	FIRE PROTECTION SYSTEM
30	FLOOR COVERING RESILIENT
31	FRAMING & ROUGH CARPENTRY
32	GLASS, GLAZING, & STOREFRONTS
33	HOUSEWARES DINING & FLAT
34	HAZARDOUS MATERIAL REMEDIATION
35	HAZERDOUS MATERIALS & REMEDIATIN
36	HEAT, VENT & AIR CONDITIONING
37	INSULATION MECHANICAL
38	INSULATION
39	LABOR ONLY
40	LAWN, GARDEN & PATIO
41	LINNENS & SOFTGOODS
42	LIGHT FIXTURES
43	LANDSCAPING
44	MASONRY
45	MARBLE
46	MOISTURE PROTECTION
47	MIRRORS & SHOWER DOORS
48	MOBILE HOMES, SKIRTING & SETUP
49	METAL STRUCTURES & COMPONENTS
50	ORNAMENTAL IRON
51	INTERIOR LATH & PLASTER
52	PLUMBING
53	PANNELING & WOOD WALL FINISHES
54	PAINTING
55	SWIMMING POOLS & SPAS
56	ROOFING
57	SCAFFOLDING
58	SIDING
59	SOFFIT, FASCIA, & GUTTER
60	SPRINKLERS
61	STAIRS
62	STEEL JOIST COMPONENTS
63	STEEL COMPONENTS
64	STUCCO & EXTERIOR PLASTER
65	SUPERVISION
66	TOILET & BATH ACCESSORIES
67	TILE
68	TIMBER FRAMING
69	TEMPORARY REPAIRS
70	WINDOWS ALUMINUM
71	SLIDING PATIO DOOR
72	WINDOW REGLAZING & REPAIR
73	WINDOW TREATMENT
74	WINDOWS - VINYL
75	WINDOWS WOOD
76	WALLPAPER
77	WATER EXTRACTION & REMEDIATION
78	EXTERIOR STRUCTURES
CostCodes

 

[Fluff]

Thanks for that, is the data to be "cleaned" in col W or col D?

 

[lYoung]

That's interesting. In my spreadsheet/range that is not column D it is Column W, not sure why it shows column D.
Great big Thank YOU for your help!

 

[Fluff]

Great big Thank YOU for your help!

Does this mean it's sorted?

 

[lYoung]

No column W (D) is not sorted, it is how it pulls from the database. Believe it sorts by date of entry which is not on the sheet.

 

[Fluff]

I meant is the code now working?

 

[lYoung]

oh, sorry, no, not working.
Just thank you for your help thus far. I was completely loss. I'm studying VBA now, however, not knowledgeable enough to do this it appears and I need this done as soon as possible.

 

[Fluff]

Not sure what's happening, because if I start off with this

+Fluff.xlsm
	W
1	Cost Code Drescription
2	APPLIANCES
3	CABINETRY
4	CLEANING
5	CONCRETE & ASPHALT
6	CON:SUBCONTRACT
7	CONT: PACKING,HANDLNG,STORAGE
8	CONTENT MANIPULATION
9	CONTENTS
10	COUNTERTOP
11	DEBRIS DISPOSAL
12	GENERAL DEMOLITION
13	DOORS
14	DRYWALL
15	ELECTRICAL
16	FLOOR COVERING - CARPET
17	FLOOR COVERING - STONE
18	FLOOR COVERING STONE
19	FLOOR COVERING - CERAMIC TILE
20	FLOOR COVERING TILE
21	FLOOR COVERING - VINYL
22	FLOOR COVERING VINYL
23	FLOOR COVERING - WOOD
24	PERMITS AND FEES
25	FENCING
26	FINISH CARPENTRY / TRIMWORK
27	FINISH HARDWARE
28	FIREPLACE
29	FIRE PROTECTION SYSTEM
30	FLOOR COVERING RESILIENT
31	FRAMING & ROUGH CARPENTRY
32	GLASS, GLAZING, & STOREFRONTS
33	HOUSEWARES DINING & FLAT
34	HAZARDOUS MATERIAL REMEDIATION
35	HAZERDOUS MATERIALS & REMEDIATIN
36	HEAT, VENT & AIR CONDITIONING
37	INSULATION MECHANICAL
38	INSULATION
39	LABOR ONLY
40	LAWN, GARDEN & PATIO
41	LINNENS & SOFTGOODS
42	LIGHT FIXTURES
43	LANDSCAPING
44	MASONRY
45	MARBLE
46	MOISTURE PROTECTION
47	MIRRORS & SHOWER DOORS
48	MOBILE HOMES, SKIRTING & SETUP
49	METAL STRUCTURES & COMPONENTS
50	ORNAMENTAL IRON
51	INTERIOR LATH & PLASTER
52	PLUMBING
53	PANNELING & WOOD WALL FINISHES
54	PAINTING
55	SWIMMING POOLS & SPAS
56	ROOFING
57	SCAFFOLDING
58	SIDING
59	SOFFIT, FASCIA, & GUTTER
60	SPRINKLERS
61	STAIRS
62	STEEL JOIST COMPONENTS
63	STEEL COMPONENTS
64	STUCCO & EXTERIOR PLASTER
65	SUPERVISION
66	TOILET & BATH ACCESSORIES
67	TILE
68	TIMBER FRAMING
69	TEMPORARY REPAIRS
70	WINDOWS ALUMINUM
71	SLIDING PATIO DOOR
72	WINDOW REGLAZING & REPAIR
73	WINDOW TREATMENT
74	WINDOWS - VINYL
75	WINDOWS WOOD
76	WALLPAPER
77	WATER EXTRACTION & REMEDIATION
78	EXTERIOR STRUCTURES
Sheet2

I end up with

+Fluff.xlsm
	W
1	Cost Code Drescription
2	APP
3	CAB
4	CLN
5	CNC
6	CON
7	CON
8	CON
9	CON
10	CTP
11	DEBRIS DISPOSAL
12	DMO
13	DOR
14	DRY
15	ELE
16	FCC
17	FCR
18	FCR
19	FCT
20	FCT
21	FCV
22	FCV
23	FCW
24	FEES
25	FEN
26	FNC
27	FNH
28	FPL
29	FPS
30	FRC
31	FRM
32	GLS
33	HDF
34	HMR
35	HMR
36	HVC
37	INM
38	INS
39	LAB
40	LGP
41	LIN
42	LIT
43	LND
44	MAS
45	MBL
46	MPR
47	MIRRORS & SHOWER DOR
48	MSK
49	MTL
50	ORI
51	PLA
52	PLM
53	PNL
54	PNT
55	POL
56	RFG
57	SCF
58	SDG
59	SFG
60	SPR
61	ST
62	STJ
63	STL
64	STU
65	SUP
66	TBA
67	TIL
68	TMB
69	TMP
70	WDA
71	WDP
72	WDR
73	WDT
74	WDV
75	WDW
76	WPR
77	WTR
78	XST
Sheet2

 

[Fluff]

The "MIRRORS & SHOWER DOR" remains as it is misspelt.