Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
can I save a file of multiple names to replace using excell 03
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
can I save a file of multiple names to replace using excell 03
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing a comma separated text file and save it. | Excel Discussion (Misc queries) | |||
save to same file as opened | Excel Discussion (Misc queries) | |||
changing file names | Excel Discussion (Misc queries) | |||
save excel file from a table delimited file (.txt) using macros | New Users to Excel | |||
Save & Save As features in file menu of Excel | Excel Discussion (Misc queries) |