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

  • Thread starter Greg is working hard.
  • Start date
G

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
 
D

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
 
D

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