Find & Replace in all Workbook (Read all Names from Specific Column)

[dim06]

Hi all,

I have four tabs (2009, 2010, 2011, 2012) with more than 2000 rows with names mixed with characters in parenthesis and codes (lets call them mixed-names) in columns (B1:B2000). In another tab: References, I have a column (D1:D1500) with names (lets call them unique names) which are part of the 'mixed-names'.

Just an example: Mixed names: BbExcelforum(04356), tta56(VBAexcel)&port

Unique Names: Excelforum, VBAexcel (which are recorded in the tab: References, Column D1:D1500).

Is there any way to read the names in column D1:D1500 (if they are part of the mixed names) and replace the mixed names with the unique names?

I have found this code but this only reads manually and I have to specify each time the type of search I want, but with 1500 names..its being quite tough.

Sub ReplaceAll()

Dim Wks As Worksheet

For Each Wks In Worksheets
If Wks.Name <> "Sheet1" Then
Wks.UsedRange.Replace _
What:="TIA", Replacement:="TI/A", _
SearchOrder:=xlByColumns, MatchCase:=True
Wks.UsedRange.Replace _
What:="TI.A", Replacement:="TI/A", _
SearchOrder:=xlByColumns, MatchCase:=True
End If
Next Wks

End Sub

Any help? Thanks a lot.

[arlu1201]

Attach a sample file with dummy data so we can help you better.

[dim06]

sample.xlsx

I attached the file, I give only a sample; the rows are over 2000 in my original one but the format is the one I have attached here. Thank you very much, I am pleased for your effort

[dim06]

any help? I am afraid there is no similar post in the past

[nilem]

Hi dim06. Try it

Please Login or Register  to view this content.

And dim06, please edit your post #1 (add code tags).

[johnjohns]

There was a silmilar query in this forum and I have incorporated the solution formula in your w.book. Again I don't remember who gave that answer. It is not me anyway

[dim06]

Thanks a lot. I am deeply pleased for your time. I will try and I will post