Find and replace according to conversion table

[efrancis]

Hi,

I have a question about automating find and replace for text data. I
havetbe replacements I want to take place. Instances of A1 should be
replaced with the value in B1, A2 with B2, and so on. There are
several thousand entries. How can I use this information to do a
large-scale search and replace conversion on data listed in a third
column? The third column contains text characters.

So,

A1 = A, B1 = D
A2 = B, B2 = E
A3 = C, B3 = F

and I want this information to convert

C1 = ABC

to

D1 = DEF

Can it be done?

 

[Dave Peterson]

How about something like:

Option Explicit
Sub testme()
Dim curWks As Worksheet
Dim myCell As Range
Dim myRng As Range

Set curWks = Worksheets("Sheet1")

With curWks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))

For Each myCell In myRng.Cells
.Range("C:C").Cells.Replace what:=myCell.Value, _
replacement:=myCell.Offset(0, 1).Value, _
lookat:=xlPart, MatchCase:=False, _
searchorder:=xlByRows
Next myCell
End With

End Sub



If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[efrancis]

Thanks a lot for the help, Dave. There are still a couple things t
resolve.

First, one quirky thing about my data is that the data I am replacin
with (column B) is made up of 4-digit number strings, i.e. "0001" o
"7845". I need these strings to remain intact when they are importe
into column C. Right now, I lose the leading zeroes.

Second, what can I do to keep the script from hanging at completion?
It just hourglasses after converting the data.

Thoughts?



I took a look at the macro primer site, too - thanks for the tip.

E

 

[Dave Peterson]

So the values in column C are text and you want to keep them as text?

You don't want to change it to numbers and just use formatting to show the
leading 0's?

Even if you do this change manually, excel sees it as a number (not as Text).

One way around this is to cycle though all the cells and do the replacements
that way. But I gotta believe that would be slower than the mass changes.

So how about this...

The macro inserts a new column D.
Takes the value in column C and puts it in column D, but with a prefix character
(I used char(1)--very rarely found in a worksheet).

Then do all the changes to column D and strip that prefix character out of
column D and plop it back into column C.

It worked fine in my simple testing--but try it against a copy of your workbook
(or close without saving if it's wrong!).

Option Explicit
Sub testme()
Dim curWks As Worksheet
Dim myCell As Range
Dim myFromRng As Range
Dim myRngToChange As Range
Dim LastRowInColC As Long
Dim CalcMode As Long

Application.ScreenUpdating = False
CalcMode = Application.Calculation

Set curWks = Worksheets("Sheet1")

With curWks

Set myFromRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))

.Range("d1").EntireColumn.Insert
LastRowInColC = .Cells(.Rows.Count, "C").End(xlUp).Row
Set myRngToChange = .Range("d1:d" & LastRowInColC)
With myRngToChange
.NumberFormat = "General"
.Formula = "=char(1)&c1"
.Value = .Value
End With

Application.Calculation = xlCalculationManual
For Each myCell In myFromRng.Cells
Application.StatusBar = "Processing #: " & myCell.Row _
& " of: " & myRngToChange.Cells.Count & " at: " & Now
myRngToChange.Cells.Replace what:=myCell.Value, _
replacement:=myCell.Offset(0, 1).Value, _
lookat:=xlPart, MatchCase:=False, _
searchorder:=xlByRows
Next myCell
Application.Calculation = CalcMode
Application.StatusBar = False

With .Range("c1:c" & LastRowInColC)
.NumberFormat = "General"
.Formula = "=mid(d1,2,1000)"
.NumberFormat = "@"
.Value = .Value
End With

.Range("D1").EntireColumn.Delete

End With

Application.ScreenUpdating = True

End Sub

And I don't know why it's locking up for you. Maybe if we turn calculation off
and do the changes, then toggle it back on, it would speed it up (maybe it
wasn't done--not really locked up.)

Since this turns calculation off, you'll have to change it back to what you had
(tools|options|calculation tab) if you interrupt it.