| Home |
| Search |
| Today's Posts |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to change multiple values in a column
That's exactly what I was looking for. Thanks
Norman Jones wrote: Hi Wade, Try something like: '============= Public Sub Tester001() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim iLastRow As Long Dim arrIn As Variant Dim arrOut As Variant Dim i As Long Set WB = Workbooks("MyBook.xls") '<<==== CHANGE Set SH = WB.Sheets("Sheet1") '<<==== CHANGE arrIn = Array("Anne", "Ben", "Carol", _ "David", "Ewan") '<<==== CHANGE arrOut = Array("Red", "Blue", "Green", _ "Yellow", "Brown") '<<==== CHANGE iLastRow = SH.Cells(Rows.Count, "A").End(xlUp).Row Set rng = Range("A2:A" & iLastRow) '<<==== CHANGE On Error GoTo XIT Application.ScreenUpdating = False For i = LBound(arrIn) To UBound(arrIn) rng.Replace What:=arrIn(i), _ Replacement:=arrOut(i), _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False Next i XIT: Application.ScreenUpdating = True End Sub '<<============= --- Regards, Norman "mrwawa" wrote in message ups.com... Thanks Mr. Jones, That is good advice. I realize that I need to learn more about macros, programming etc. I will try using the macro recorder. Norman Jones wrote: Hi MrWawa, I am wondering if anyone knows a way to change a list of values in a column with either an Excel function or a macro. The column is in text format and I have a list of text strings that I want to revise As Franz indicates, you would need to provide substantially more information to enable a respondent meaningfully to assist you. I can do it by filtering, but I may need to do the same process later. This seems like a job for a macro, but am a novice at using them. Try turning on the macro recorder while you perform the necessary operations manually. This will produce code which can be edited to enable more generic application. If you experience problems in adapting the recorder code, post back with the problematic code. As you are not familiar with macros, you may wish to visit David McRitchie's 'Getting Started With Macros And User Defined Functions' at: http://www.mvps.org/dmcritchie/excel/getstarted.htm --- Regards, Norman |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
| Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
| Closing File Error | Excel Discussion (Misc queries) | |||
| Lookup Table Dilemma | Excel Worksheet Functions | |||
| Formula to compare multiple rows values based on another column? | Excel Worksheet Functions | |||
Threaded Mode
