macro to change multiple values in a column

M

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

Thanks in advance
 
F

Franz Verga

Nel post *mrwawa* ha scritto:
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.
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.

Thanks in advance
Click to expand...

It depends on what do you whant to change in your data...

Give us some example and try to explain what are you going to do...


--
Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
N

Norman Jones

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
Click to expand...

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.
Click to expand...

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
 
M

mrwawa

Sorry, I quess I was too concise.

I have a column that is 25000 rows. Each one of the cells contains one
of 598 values. The values are codes (ex: ACERRUB or MAGNVIR) and I
want to replace roughly 100 of these codes with different codes (ex.
replace ACERRUB with ACERDRU). I can do this manually (edit,replace)
but I am wondering if there is a macro available with case statements.
I have a little programming experience, but not with Excel, so an
example would get me started.

Wade
 
M

mrwawa

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.
 
N

Norman Jones

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 said:
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.
Click to expand...
 
M

mrwawa

That's exactly what I was looking for. Thanks


Norman said:
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
'<<=============
Click to expand...
 
Top