can I save a file of multiple names to replace using excell 03

[Greg is working hard.]

I have a list of approx 300 names that must be replaced with abbreviations in
excell. I must do this every few days. Is there a way to save the multiple
replacement values into a command so I can replace the same list in a single
step?

Thank You

[Dave Peterson]

I'd create another worksheet.

Column A would hold the old words
column B would hold the abbreviations

Then have a macro that would loop through those cells to do mass changes.

Something like:

Option Explicit
Sub testme()

Dim ListWks As Worksheet
Dim WksToFix As Worksheet
Dim myCell As Range
Dim myRng As Range

Set ListWks = ThisWorkbook.Worksheets("sheet1")
Set WksToFix = ActiveSheet

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

For Each myCell In myRng.Cells
WksToFix.Cells.Replace what:=myCell.Value, _
replacement:=myCell.Offset(0, 1).Value, _
searchorder:=xlByRows, MatchCase:=xlNo, _
lookat:=xlWhole
Next myCell
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

Greg is working hard. wrote:
>
> I have a list of approx 300 names that must be replaced with abbreviations in
> excell. I must do this every few days. Is there a way to save the multiple
> replacement values into a command so I can replace the same list in a single
> step?
>
> Thank You

--

Dave Peterson

[Dave Peterson]

You could even put that worksheet with the list in another workbook. Just put
the macro in that workbook, too.

Then open that workbook when you need to do the work.

Open your workbook to fix.
Activated the sheet to fix.

Alt-f8 to see the list of macros and run TestMe. (Rename that to something
better, though.)

Dave Peterson wrote:
>
> I'd create another worksheet.
>
> Column A would hold the old words
> column B would hold the abbreviations
>
> Then have a macro that would loop through those cells to do mass changes.
>
> Something like:
>
> Option Explicit
> Sub testme()
>
> Dim ListWks As Worksheet
> Dim WksToFix As Worksheet
> Dim myCell As Range
> Dim myRng As Range
>
> Set ListWks = ThisWorkbook.Worksheets("sheet1")
> Set WksToFix = ActiveSheet
>
> With ListWks
> Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
> End With
>
> For Each myCell In myRng.Cells
> WksToFix.Cells.Replace what:=myCell.Value, _
> replacement:=myCell.Offset(0, 1).Value, _
> searchorder:=xlByRows, MatchCase:=xlNo, _
> lookat:=xlWhole
> Next myCell
> 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
>
> Greg is working hard. wrote:
> >
> > I have a list of approx 300 names that must be replaced with abbreviations in
> > excell. I must do this every few days. Is there a way to save the multiple
> > replacement values into a command so I can replace the same list in a single
> > step?
> >
> > Thank You
>
> --
>
> Dave Peterson

--

Dave Peterson