View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.newusers
Roger Govier[_3_] Roger Govier[_3_] is offline
external usenet poster
 
Posts: 2,480
Default Find and replace

Hi

The minor modification of the code worked fine for me
Sub transla()
'GS .newusers
Dim inn() As String, outt() As String
Dim n As Long, i As Long, r As Range, v As Variant
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


--
Regards
Roger Govier

"alistairp" wrote in message
...
Thanks Max

This works until the final step when all data within the worksheet is
being
amended.

I need to Macro to say something similar to the tick box in Find & Replace
that says 'Match entire cell contents' only.

For example I am amending OS to read OXFORD STREET. OS is in Column A and
despite highlighting this column when I click Run all text in the
spreadsheet
is altered. The word MOSAIC is being amended to MOXFORDSTREETAIC and then
it
is adding READING in place of RD and giving me MOXFOREADINGSTREETAIC.

Presumably I have missed something.

Any further assistance would be appreciated.

Alistair


"Max" wrote:

One way is to use a sub posted by Gary''s Student [Sub transla() below]
which
can do all of your 26 multiple find n replace at one go

Here's the easy steps to implement:
(Try on a spare copy of your file)

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 (below) into the code window
(everything within the dotted lines)

'----------
Sub transla()
'GS .newusers
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
'---------

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 (ie list your 26 2 digit
labels)
List the corresponding full 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 (this text could be all over the place within the sheet)

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.

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
"alistairp" wrote:
I receive data once a day into an Excel spreadsheet and need to convert
26 2
digit different labels to words.

For example RD needs to be converted to READING.

I am using find and replace but the data does not save for the next
day.

Is there any way that I can create 26 templates that I can then use
without
having to type the full words every day.