Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / April 2008

Tip: Looking for answers? Try searching our database.

multiple sets words replace

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
dk - 07 Apr 2008 16:17 GMT
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
Gary''s Student - 07 Apr 2008 17:13 GMT
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

Signature

Gary''s Student - gsnu2007g

> Is there anyway to make a listinng for find & Replace
> ex;if=then
[quoted text clipped - 3 lines]
> 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
dk - 07 Apr 2008 21:07 GMT
Can you please explain all steps
Thank You

> 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
[quoted text clipped - 27 lines]
> > 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
Max - 08 Apr 2008 04:45 GMT
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.
Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

dk - 09 Apr 2008 22:05 GMT
We are getting runtime error  13 error 2029

> Here's the easy steps to implement GS' subroutine suggestion ..
> (Thought it was a good suggestion by GS, btw)
[quoted text clipped - 34 lines]
>
> Last but not least, do go back to GS' response and click the "Yes" button.
Max - 10 Apr 2008 00:20 GMT
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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.