multiple sets words replace

  • Thread starter Thread starter dk
  • Start date Start date
D

dk

Is there anyway to make a listinng for find & Replace
ex;if=then
out=like
hello=go
we have about 100 different words that we want to replace with others
so we want to make a txt or other file of the full list & then run in excel
or word a 1 step find replace
 
This is just an example. We have a worksheet called "xlator". The
translation table is in columns A & B. The text to be editted is in
"Sheet2". Enter and run the following macro:

Sub transla()
Dim inn() As String, outt() As String
Sheets("xlator").Activate
n = Cells(Rows.Count, 1).End(xlUp).Row
ReDim inn(1 To n), outt(1 To n)
For i = 1 To n
inn(i) = Cells(i, 1).Value
outt(i) = Cells(i, 2).Value
Next

Sheets("Sheet2").Activate
For Each r In ActiveSheet.UsedRange
v = r.Value
For i = 1 To n
v = Replace(v, inn(i), outt(i))
Next
r.Value = v
Next
End Sub
 
Can you please explain all steps
Thank You

Gary''s Student said:
This is just an example. We have a worksheet called "xlator". The
translation table is in columns A & B. The text to be editted is in
"Sheet2". Enter and run the following macro:

Sub transla()
Dim inn() As String, outt() As String
Sheets("xlator").Activate
n = Cells(Rows.Count, 1).End(xlUp).Row
ReDim inn(1 To n), outt(1 To n)
For i = 1 To n
inn(i) = Cells(i, 1).Value
outt(i) = Cells(i, 2).Value
Next

Sheets("Sheet2").Activate
For Each r In ActiveSheet.UsedRange
v = r.Value
For i = 1 To n
v = Replace(v, inn(i), outt(i))
Next
r.Value = v
Next
End Sub
Click to expand...
 
Here's the easy steps to implement GS' subroutine suggestion ..
(Thought it was a good suggestion by GS, btw)

Install GS' sub into a regular module as below:
In your excel file:
1. Press Alt+F11 to go to VBE
In VBE, click Insert > Module
Copy n paste GS' sub into the code window
(everything within the dotted lines from line: Sub transla() ... to the
line: End sub, as indicatively shown below. Go back to GS' response to do the
actual copying)

'----------
Sub transla()
....
End sub
'---------

Press Alt+Q to get back to Excel

2. In Excel,
a. Insert a new sheet, name it as: xlator
List the text that you want to find in col A, and the corresponding text to
replace it with in col B

b. Go to the sheet that you have the text to be found n replaced all at one
go. Rename the sheet as: Sheet2

Alternatively, you could go back to VBE and amend this line in the sub to
reflect your actual sheetname:

Sheets("Sheet2").Activate

c. Press Alt+F8 to bring up the Macro dialog
Either double-click directly on "transla" inside the window,
or select "transla", click Run, to run the sub.

Last but not least, do go back to GS' response and click the "Yes" button.

---
 
I'm not sure what happened over there when you tried it out ..
(I don't know whether GS will come back here)

Here's my offer to you:
Download this sample file
It has GS' sub implemented & a set-up with dummy data
Try it out in Sheet2 (easy steps given):
http://www.freefilehosting.net/download/3f41e
Sub to Find n Replace.xls


---
 
Back
Top