ExcelBanter - macro to change multiple values in a column

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - macro to change multiple values in a column (https://www.excelbanter.com/excel-discussion-misc-queries/97147-macro-change-multiple-values-column.html)

macro to change multiple values in a column

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

macro to change multiple values in a column

Nel post oups.com
*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

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

macro to change multiple values in a column

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

macro to change multiple values in a column

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

Franz Verga wrote:
Nel post oups.com
*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

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

macro to change multiple values in a column

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

macro to change multiple values in a column

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

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