View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson Dave Peterson is offline
external usenet poster
 
Posts: 35,218
Default can I save a file of multiple names to replace using excell 03

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