find replace VBA

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

0
4/2/2007 10:59:52 PM
excel 39879 articles. 2 followers. Follow

10 Replies
475 Views

Similar Articles

[PageSpeed] 18

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


"michael.boucher@gmail.com" wrote:

> 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).
> 
> 
0
4/3/2007 1:08:03 AM
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

"michael.boucher@gmail.com" wrote:
> 
> 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).

-- 

Dave Peterson
0
petersod (12005)
4/3/2007 1:30:56 AM
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@gmail.com> wrote in message 
news:1175554792.643956.172820@p77g2000hsh.googlegroups.com...
>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).
> 


0
4/3/2007 3:36:50 AM
It reports that ColWks is not definied. How should I continue?

On Apr 2, 9:30 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> 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
>
>     WithColWks.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
>
>
>
> "michael.bouc...@gmail.com" wrote:
>
> > 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).
>
> --
>
> Dave Peterson


0
4/3/2007 3:37:14 AM
I'm guessing ColWks is really listwks.

"michael.boucher@gmail.com" wrote:

> It reports that ColWks is not definied. How should I continue?
> 
> On Apr 2, 9:30 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > 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
> >
> >     WithColWks.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
> >
> >
> >
> > "michael.bouc...@gmail.com" wrote:
> >
> > > 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).
> >
> > --
> >
> > Dave Peterson
> 
> 
> 
0
4/3/2007 10:26:00 AM
You are correct!

Thanks for sharing the fix.

Barb Reinhardt wrote:
> 
> I'm guessing ColWks is really listwks.
> 
> "michael.boucher@gmail.com" wrote:
> 
> > It reports that ColWks is not definied. How should I continue?
> >
> > On Apr 2, 9:30 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > 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
> > >
> > >     WithColWks.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
> > >
> > >
> > >
> > > "michael.bouc...@gmail.com" wrote:
> > >
> > > > 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).
> > >
> > > --
> > >
> > > Dave Peterson
> >
> >
> >

-- 

Dave Peterson
0
petersod (12005)
4/3/2007 12:43:23 PM
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
SalesAid Software
dguillett1@austin.rr.com
<michael.boucher@gmail.com> wrote in message 
news:1175554792.643956.172820@p77g2000hsh.googlegroups.com...
>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).
> 


0
dguillett1 (2487)
4/3/2007 1:35:59 PM
Why not the whole range instead of each cell, one at a time.

-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:4611AE50.93E0F6BC@verizonXSPAM.net...
> 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
>
> "michael.boucher@gmail.com" wrote:
>>
>> 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).
>
> -- 
>
> Dave Peterson 


0
dguillett1 (2487)
4/7/2007 6:45:58 PM
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 wrote:
> 
> Why not the whole range instead of each cell, one at a time.
> 
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:4611AE50.93E0F6BC@verizonXSPAM.net...
> > 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
> >
> > "michael.boucher@gmail.com" wrote:
> >>
> >> 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).
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12005)
4/7/2007 7:05:20 PM
The first time, I miss read. I now see that it does the same as mine. Just 
takes a bit longer to read <G>

-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:4617EB70.2B38ADBB@verizonXSPAM.net...
> 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 wrote:
>>
>> Why not the whole range instead of each cell, one at a time.
>>
>> --
>> Don Guillett
>> SalesAid Software
>> dguillett1@austin.rr.com
>> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>> news:4611AE50.93E0F6BC@verizonXSPAM.net...
>> > 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
>> >
>> > "michael.boucher@gmail.com" wrote:
>> >>
>> >> 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).
>> >
>> > --
>> >
>> > Dave Peterson
>
> -- 
>
> Dave Peterson 


0
dguillett1 (2487)
4/7/2007 8:09:17 PM
Reply:

Similar Artilces:

searching personel folders with subfolders ticked not finding contacts etc
Hi I have just noticed that the search i often use, which i thought covered all folders does not. if i click personel folders and tick subfolders, it is not finding most contacts, any ideas? thanks tim ...

Excel Macro VBA Help Needed
I have a sheet which has been copied from a pivot table. I need it to perform the following: Find a cell with specific text (Yes) Move over three columns (I have been using an offset) Write a formula that subtotals data that is below this summation row Copies this formula to right until the column is empty Display this data onto a new summary sheet Ultimately, this would be a loop that looks in column C for some wildcard, subtotals and then, moves over to column B and totals the previously calc'd subtotals. I seem to be able to get to the specific text cell and use the offset. I am str...

Serach don't find it all
Hi... Recently I noticed that during a search in the contacts folder some contacts are not found. I can scroll down and see them in the list and open them that way, but they do not appear in a search. I have Outlook 2007 installed with Xobni addon installed on a Norwegian Windows Vista Ultimate home made computer. How do I reindex? Can Xobni cause trouble? -- Vennlig hilsen �yvind Granberg tresfjording@live.no www.tresfjording.com ...

O2003: Find Related Messages
When I attempt to use the "Find Related Messages" feature in Outlook 2003 I receive the following error message: Outlook cannot perform your search. Outlook cannot display this view. The folders specified are not available. I followed the directions in KB240697 (which refers to an error message of "Outlook cannot perform your search. Outlook cannot display this view. The folder already exists."), attempting to rectify the problem to no avail. Any suggestions on how to get this valuable feature to work properly, it use too. Referenced KB article: http://support.microsoft....

how to replace fields
Hi all, I am new to Ms access, would like to know how to replace fields with some specific values, i.e. want to replace all records with a field named MONTH by "May". What should I do? Thanks. thanks a lot, will change the field name as advised. I am just rewritting some Visual Fox-pro programs by Ms Access as our copy of VFP is in very low version that our PC can't support. The purpose of the program I am studying is to import some monthly report data and append to a master file. That's why we have to replace the newly added records by the current month value ...

vba excel project
im not sure if this is possible in excel... but here's what my boss wants... one excel file (master file) contains 10 command buttons (sheet 1). when a user clicks on a button (button 1), an excel template file (template 1) is automatically opened... now this template file will be filled up by the user, and once she/he saves the template (template 1) with a new filename, the date and time (of saving) will be listed on the master file (sheet 2) together with the specified filename of the user. now if the user clicks on button 2, template 2 is opened. and then once saved, the date,time a...

AfxBeginThread failure
In an MFC pgm under VS2005sp1, I am creating a worker thread using AfxBeginThread. Under certain circumstances which I can't quite pin down, AfxBeginThread returns NULL, indicating it has failed. Is there any way I can find out the cause of the failure, such as an error code? I have tried tracing through the code with the debugger, but what is apparently detecting the error condition is in MS code for which I don't have the source. My specific code is CWinThread* pThread=AfxBeginThread(MyControlFunction,&ti,0/*priority*/, 230000000/*stack size*/); and a test for pThread==NULL im...

New Disk
Hi I have just installed a new disk as a primary disk and installed winxp pro and office pro on it. I have then hooked my old disk up as a slave and wanted to copy my outlook data file onto my new disk. The problem is I can't find any .pst files. So my question is where would my outlook file be and what will it be called ? Thanks ...

How to find the check form to use for GP 9.0
I don't want to use Deluxe as our check vendor. I want to use Harland. Microsoft MBS sales just gives us Deluxe's phone number, when we request help with obtaining the correct format for Harland checks. Harland has two check formats for GP payable checks. 1) Great Plains Accounting 7.0 - 9.0 2) Great Plains Dynamics Harland isn't sure and can't reference Deluxe. Thanks, John John, From what I can tell from Harland's website, you want the Laser Multi-Purpose checks they show under Microsoft Great Plains. That's the one with the check in the middle, stub on t...

Problems finding a file on a network drive in excel 2007
I have updated form excel 2003 to excel 2007. When I run my code in 2003 the network files I am using are found by the program, but if I run the same code using office 2007 I get a message stating the file cannot be found. Any Suggestions? Some functionality that worked in 2003, is no longer supported in 2007. What exactly is the problem? Barb Reinhardt "Lori Rice" wrote: > I have updated form excel 2003 to excel 2007. When I run my code in 2003 the > network files I am using are found by the program, but if I run the same code > using office 2007...

How can I replace spaces in text cells (excel) with underscores?
I've tried searching for " " and replacing it with _ but excel doesn't find any spaces with " ". use "~ " (tilde space - no quotes HTH -- AP "JB2006" <JB2006@discussions.microsoft.com> a �crit dans le message de news:481EFE49-E0C8-431F-8B4D-8BB9618EAF26@microsoft.com... > I've tried searching for " " and replacing it with _ but excel doesn't find > any spaces with " ". > That worked well, many thanks! ...

Can an Advanced Find be exported?
Do you know if an Advanced Find view can be exported Thanks, Clare when you create view , you usually share or assign to another users. I never heard of exporting it :=) /Aamir Thanks Aamir. I wanted to take it from one installation to another. Never mind. "Aamir" wrote: > > when you create view , you usually share or assign to another users. I > never heard of exporting it :=) > > /Aamir > > ...

How to find cells with non-conditional formatting?
Hi, I know how to find cells with **conditional** formatting. Is there a way to find cells that have non-conditional formats? Let me try to be a bit specific. How can I find cells that I have applied custom formats (format>cells>number>custom)? Appreciate feedback. Epinn For any particular custom format... If you're using xl2002+, you can use the Options button on the Edit|Find dialog to specify the number format to search for. Before xl2002, you'll have to loop through all the cells and look at each cell's number format. Maybe you can use some of the code here...

Updating VBA function?
Is there a way to update this function so that it executes the same set of criteria for the data on line 32, 45, 58 and so on? In other words, every 13th line has a different set of data of that I would like to have analyzed in the same way that this function analyzes the data in line 19? Everywhere line 19 and 7 is listed, the next group is line 32 and line 7 is line 20 and so on. Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'make whole sheet the target If Target.Column > 0 Then 'clear previous answer answer = "" 'see what lookup code has been g...

efficient method of finding closest date by specify input value
can it be specify the input value to the formate with YY-YY, that means let the use to key in 09-10 from the query. and the 09-10 means the date range (01/04/2009 to 31/03/2010) <staff> peter 01/08/2009 SAM 02/03/2009 IAN 03/01/2010 ==================> key in: 09-10 then, the output will shown below: output: peter 01/08/2009 IAN 03/01/2010 In query design view, enter something like this (as one line) into the Criteria row under your date field: >= DateSerial([From 1 April what year?],4,1) And < DateSerial([From 1 April what year?]+1,4,1) ...

VBA
I having problems using the My Outlook Calendar.dot template from the Slipstick website (sorry I can't attach it, file type not supported). I downloaded the .dot file and with the help of someone in IT we modified the code to pull event details from outlook onto the resulting calendar report. I was able to use it successfully for a few weeks and then all of a sudden the .dot stopped working. It gives me a runtime error whenever I try to launch it. I'm not familiar with VBA at all but I used the debug tool to see where the code "breaks". It seems to happen at L...

Separating parts of a string in VBA
Hello, I have a string in cells(1,1). The value of the cell is "DATE" 1/1/10 THRU 3/1/10" I want to separate this string into the two new strings such that one string would read "1/10/10" and the other would read "3/1/10". In C programming, this would be fairly easy. But in VBA I don't know any string functions which can read and compare individual characters. Does anyone know how to accomplish what I am trying to do? thanks IF the DATE and THRU parts of your example text (along with their adjacent blank spaces) appear in all the text s...

Out of Office Assistant is not visible, where to find?
I have Office 2002 and although the Help section says to find the autoresponder under Tools/Out of Office Assistant - it's not there. Is there somewhere I can access this functionaliy? Or is it not really included in the product? Thanks... "Eric" <efriedma@aol.com> wrote in message news:047501c3d850$e245bbb0$a101280a@phx.gbl... > I have Office 2002 and although the Help section says to > find the autoresponder under Tools/Out of Office > Assistant - it's not there. > > Is there somewhere I can access this functionaliy? Or is > it not really inclu...

An error occurred initializing the VBA LIBRARIES (65535)
When I want to open the Excel (97 version, Windows 98), I receive the following message: "An error occurred initializing the VBA libraries (65535)".When I click "OK" the Excel however opens, but can not open all of my excel documents.The same message I recceive when I want to open in Excel TOOLS/MACRO/VISUAL BASIC EDITOR. What has hapened and how can I resolve this problem? Thanks, Jovan Milojkovic, Belgrade! ...

Match Function Problem
I'm having trouble with the Match function in Excel XP. I have a list of numbers from 0.01 to 125.00 and I'm telling the match function to look down the column and find specific numbers based on user input. The problem I'm having is that certain number work and other don't...I've tried reformatting the user input cell and the Match table to every format I can think or including text and I can't get it to work. I've verified that all the numbers I'm working with are limited to just 2 decimal places. Any help??? PE, Post an example of your data, and th...

PLS Help! Can't find a presentation I've been working on for scho
I'm currently working on a presentation as a school project. My school partner sent me the powerpoing presentation to finish up on Friday. I opened up and spent the entire weekend finishing it up. It took me over 25 hours of work! It was a master peice! Then while I was finishing it up this morning it just went "poof!" And then I could not find it again anywhere! I tried to open up power point and pulled up the last 20 items listed and it didn't come up. I tried doing a search on my entire hard drive and it's not there! ANy ideas!? thank you so muc...

Add and edit a shape in VBA
Hi, I have written code to add a rectangle and a line in VBA. When I run the code it sometimes works fine. If I use F8 to step through the code the code to add the line works fine but not if I run the code as usual. The problem with the rectangle is that the text is not inserted into the rectangle. What is wrong with the code? Sub Shapes() Dim Comment As String Dim Serie_no Dim Point_no Dim Left__no Dim Top_no Serie_no = 3 Point_no = 3 ' Text from a name range Comment = Range("Comment_txt") Sheets("Chart")....

Cannot find a specific printer
In Publisher 2003 I cannot find a specific printer to Print on the peer-to-peer network. 1. The printer displayed at one time & did work. 2. In WinXP Start-->Printer and Faxes this printer is set up properly. 3. The printer works on other PCs. 4. This PC, that cannot find the printer, can find it in WORD. Any solutions? Steve What server do you use? How to troubleshoot general printing problems in Windows Server 2003 when you use Office products http://support.microsoft.com/kb/870968/en-us -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews....

Partial replace
Hello, I'm trying to search and replace but I do not want to replace the whole cell, only a few characters in them. Namely, I have list of names, some of which have two stars ** after it and I want to get rid of those 2 stars after the name. How do I do that? Thanks a lot Sandra One way .. Assuming data in A1 down, Put in B1: =TRIM(SUBSTITUTE(A1,"**"," ")) Copy down -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "Sept" <sep@sept-europe.com> wrote in message news:BEB9F138.137%sep@sept-europe.c...

VBA problems
Hello, I just started messing around with VBA and I got something approaching what I want (what I want is to highlight a row based on a value in the 1st column). This is highlighting the wrong row though! It looks like all the numbers are correct... for instance, when I step through it, I'll get the correct row selected but a different row highlighted! Please shed some light on this! It's quite frustrating. ' first column has dates from 12/2003 to 12/2006 Private Sub Worksheet_Activate() Const Yellow = 6 Dim row As String Range("A1").Select Do While...