M
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).
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).