find replace VBA

[michael.boucher]

I have a list of addresses that I want to standardize. For example, I
have this list in column A:
100 Randlett Steet
450 Park Str
Apt 5 Park Sreet
Park Streeet Apt B5

I want to standardize all the versions of 'street' to "St". There are
approximately 300 similar replacements, like Aveenue to Ave, Roade to
Rd, Northgate to 399 N 7th St. I have compiled all the misspelled
words, put them in column B, and the "correct" version next to it, in
Column C. I have done all this work because I want to be able to use
this list as we continue to receive address data with similar typos.

Now, I want to be able to take the address list, find the misspelled
word in Column B, and replace that word with the correct word in
Column C. I have found the below VBA formula below, from this group,
but I am not sure 1: if I can apply it to my situation, and 2: how to
apply it.

Any help would be appreciated.

Thanks.

Create a new workbook with a single worksheet with the old values in
column A
and the new values in column B.

Option Explicit
Sub testme()

Dim wkbk As Workbook
Dim myRng As Range
Dim myCell As Range
Dim wks As Worksheet

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

Set wkbk = ActiveWorkbook
If wkbk.FullName = ThisWorkbook.FullName Then
MsgBox "Please activate the workbook to be fixed!"
Exit Sub
End If

For Each wks In wkbk.Worksheets
For Each myCell In myRng.Cells
With wks.UsedRange
.Replace what:=myCell.Value, _
replacement:=myCell.Offset(0, 1).Value, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
MatchCase:=False
End With
Next myCell
Next wks
End Sub

And I assumed that the cost centers were in cells by themselves
(xlwhole--change
it to xlpart if there's other stuff in those cells).

 

[Guest]

WHy not use something like this

for each r in myrange
if r.value like "*Steet*" then
r.value = Replace(r.value, "Steet", "St.")
end if
next r

 

[Dave Peterson]

Option Explicit
Sub testme()
Dim ListWks As Worksheet
Dim myList As Range
Dim myCell As Range

Set ListWks = worksheets("Sheet99999") '<-- change this

With ListWks
Set myList = .Range("B1", .Cells(.Rows.Count, "B").End(xlUp))
End With

With ColWks.Range("a:a")
For Each myCell In myList.Cells
.Cells.Replace What:=myCell.Value, _
Replacement:=mycell.offset(0,1).value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
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

 

[Bob Flanagan]

Michael, if you don't want to do your own macros, take a look at the Data
Cleaner at http://www.add-ins.com/datacleaner.htm

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[michael.boucher]

It reports that ColWks is not definied. How should I continue?

 

[Guest]

I'm guessing ColWks is really listwks.

It reports that ColWks is not definied. How should I continue?

 

[Dave Peterson]

You are correct!

Thanks for sharing the fix.

I'm guessing ColWks is really listwks.

 

[Don Guillett]

No need for a helper column. Just make a list of items to correct and loop
thru
Sub FixStreet()
For Each c In Range("d2:d5")'list of things to replace
Range("b2:b5").Replace c, "St", LookAt:=xlPart
Next c
End Sub

 

[Don Guillett]

Why not the whole range instead of each cell, one at a time.

 

[Dave Peterson]

It does an Edit|Replace against all of column A.

But it does cycle through all the cells in column B (and column C) to find what
should be replaced with what.

 

[Don Guillett]

The first time, I miss read. I now see that it does the same as mine. Just
takes a bit longer to read <G>