How to automate process of replacing values?

[J. Gutierrez]

OK, let me briefly explain...I work in a medical research lab, and part of my
job is organizing excel files containing patient medical info. The data is
imported as a .csv file from a large cancer registry. There are columns of
data where a three digit number represents text. (example: 000 is heart, 001
is lung, etc.) In order to be able to work with the data, I have to replace
the numbers with the corresponding text. (The values all come out of a
standardized reference) Currently, I have to do this manually, using the Find
and Replace window in excel. With over 900 values to replace, it gets
tedious, especially when I have to repeat the whole process for every project
that we start.

There must be a way to automatically make all of the replacements in the
spreadsheet.....any suggestions?

Thanks in advance for any help.

JG

[Dave Peterson]

Create a new workbook.
Put the values 000 to 900 in column A.
If those values are brought in as 0, 1, ..., 900 in the .CSV file, and you give
them a custom format of 000, then do the same thing in this column.

Put the strings that do the replacements in column B.

(Yep, you'll have a macro that just does the edit|replace 900 times.)

Then put this code into a general module of that same workbook.

Option Explicit
Sub DoLotsOfChanges()

Dim wks As Worksheet
Dim tableWks As Worksheet
Dim myCell As Range
Dim myRng As Range

Set wks = ActiveSheet
Set tableWks = ThisWorkbook.Worksheets("sheet1")

With tableWks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With


For Each myCell In myRng.Cells
wks.Range("a1,d1,f1").EntireColumn.Replace what:=myCell.Value, _
replacement:=myCell.Offset(0, 1).Value, _
lookat:=xlWhole, searchorder:=xlByRows, _
MatchCase:=False
Next myCell

End Sub

Adjust this line:
wks.Range("a1,d1,f1")
to match the columns that need the "fixing".

Now save this workbook as a nice name.

when you get the next .csv file...
Open the workbook with the code and table.
then open the .csv file.

Make sure you're on the worksheet that needs fixing.

Tools|Macro|macros|
click on DoLotsOfChanges
and click run.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



J. Gutierrez wrote:
>
> OK, let me briefly explain...I work in a medical research lab, and part of my
> job is organizing excel files containing patient medical info. The data is
> imported as a .csv file from a large cancer registry. There are columns of
> data where a three digit number represents text. (example: 000 is heart, 001
> is lung, etc.) In order to be able to work with the data, I have to replace
> the numbers with the corresponding text. (The values all come out of a
> standardized reference) Currently, I have to do this manually, using the Find
> and Replace window in excel. With over 900 values to replace, it gets
> tedious, especially when I have to repeat the whole process for every project
> that we start.
>
> There must be a way to automatically make all of the replacements in the
> spreadsheet.....any suggestions?
>
> Thanks in advance for any help.
>
> JG

--

Dave Peterson

[J. Gutierrez]

Thanks for the reply....I've listed the numbers 000-999 and their replacement
strings in columns A and B, but where do I enter the code for the macro?

Thanks,
JG

"Dave Peterson" wrote:
> Create a new workbook.
> Put the values 000 to 900 in column A.
> If those values are brought in as 0, 1, ..., 900 in the .CSV file, and you give
> them a custom format of 000, then do the same thing in this column.
>
> Put the strings that do the replacements in column B.
>
> (Yep, you'll have a macro that just does the edit|replace 900 times.)
>
> Then put this code into a general module of that same workbook.
>
> Option Explicit
> Sub DoLotsOfChanges()
>
> Dim wks As Worksheet
> Dim tableWks As Worksheet
> Dim myCell As Range
> Dim myRng As Range
>
> Set wks = ActiveSheet
> Set tableWks = ThisWorkbook.Worksheets("sheet1")
>
> With tableWks
> Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
> End With
>
>
> For Each myCell In myRng.Cells
> wks.Range("a1,d1,f1").EntireColumn.Replace what:=myCell.Value, _
> replacement:=myCell.Offset(0, 1).Value, _
> lookat:=xlWhole, searchorder:=xlByRows, _
> MatchCase:=False
> Next myCell
>
> End Sub
>
> Adjust this line:
> wks.Range("a1,d1,f1")
> to match the columns that need the "fixing".
>
> Now save this workbook as a nice name.
>
> when you get the next .csv file...
> Open the workbook with the code and table.
> then open the .csv file.
>
> Make sure you're on the worksheet that needs fixing.
>
> Tools|Macro|macros|
> click on DoLotsOfChanges
> and click run.
>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
>
>
> J. Gutierrez wrote:
> >
> > OK, let me briefly explain...I work in a medical research lab, and part of my
> > job is organizing excel files containing patient medical info. The data is
> > imported as a .csv file from a large cancer registry. There are columns of
> > data where a three digit number represents text. (example: 000 is heart, 001
> > is lung, etc.) In order to be able to work with the data, I have to replace
> > the numbers with the corresponding text. (The values all come out of a
> > standardized reference) Currently, I have to do this manually, using the Find
> > and Replace window in excel. With over 900 values to replace, it gets
> > tedious, especially when I have to repeat the whole process for every project
> > that we start.
> >
> > There must be a way to automatically make all of the replacements in the
> > spreadsheet.....any suggestions?
> >
> > Thanks in advance for any help.
> >
> > JG
>
> --
>
> Dave Peterson
>

[Dave Peterson]

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Start a new workbook (so you can use it against any other workbook)

Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (book1)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Don't forget to fix the range to fix:
wks.Range("a1,d1,f1").......

Now go back to excel and save this workbook (so you don't have to do this
portion again).


Then test it out:

Open one of those workbooks that has a worksheet to fix.
Select one of those worksheets

tools|macro|macros...
select the macro and click run.

J. Gutierrez wrote:
>
> Thanks for the reply....I've listed the numbers 000-999 and their replacement
> strings in columns A and B, but where do I enter the code for the macro?
>
> Thanks,
> JG
>
> "Dave Peterson" wrote:
> > Create a new workbook.
> > Put the values 000 to 900 in column A.
> > If those values are brought in as 0, 1, ..., 900 in the .CSV file, and you give
> > them a custom format of 000, then do the same thing in this column.
> >
> > Put the strings that do the replacements in column B.
> >
> > (Yep, you'll have a macro that just does the edit|replace 900 times.)
> >
> > Then put this code into a general module of that same workbook.
> >
> > Option Explicit
> > Sub DoLotsOfChanges()
> >
> > Dim wks As Worksheet
> > Dim tableWks As Worksheet
> > Dim myCell As Range
> > Dim myRng As Range
> >
> > Set wks = ActiveSheet
> > Set tableWks = ThisWorkbook.Worksheets("sheet1")
> >
> > With tableWks
> > Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
> > End With
> >
> >
> > For Each myCell In myRng.Cells
> > wks.Range("a1,d1,f1").EntireColumn.Replace what:=myCell.Value, _
> > replacement:=myCell.Offset(0, 1).Value, _
> > lookat:=xlWhole, searchorder:=xlByRows, _
> > MatchCase:=False
> > Next myCell
> >
> > End Sub
> >
> > Adjust this line:
> > wks.Range("a1,d1,f1")
> > to match the columns that need the "fixing".
> >
> > Now save this workbook as a nice name.
> >
> > when you get the next .csv file...
> > Open the workbook with the code and table.
> > then open the .csv file.
> >
> > Make sure you're on the worksheet that needs fixing.
> >
> > Tools|Macro|macros|
> > click on DoLotsOfChanges
> > and click run.
> >
> > If you're new to macros, you may want to read David McRitchie's intro at:
> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >
> >
> >
> > J. Gutierrez wrote:
> > >
> > > OK, let me briefly explain...I work in a medical research lab, and part of my
> > > job is organizing excel files containing patient medical info. The data is
> > > imported as a .csv file from a large cancer registry. There are columns of
> > > data where a three digit number represents text. (example: 000 is heart, 001
> > > is lung, etc.) In order to be able to work with the data, I have to replace
> > > the numbers with the corresponding text. (The values all come out of a
> > > standardized reference) Currently, I have to do this manually, using the Find
> > > and Replace window in excel. With over 900 values to replace, it gets
> > > tedious, especially when I have to repeat the whole process for every project
> > > that we start.
> > >
> > > There must be a way to automatically make all of the replacements in the
> > > spreadsheet.....any suggestions?
> > >
> > > Thanks in advance for any help.
> > >
> > > JG
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[J. Gutierrez]

Meant to say "Thank you"...got interrupted by the hurricane last week.

Thanks.

"Dave Peterson" wrote:

> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> Short course:
>
> Start a new workbook (so you can use it against any other workbook)
>
> Hit alt-f11 to get to the VBE (where macros/UDF's live)
> hit ctrl-R to view the project explorer
> Find your workbook.
> should look like: VBAProject (book1)
>
> right click on the project name
> Insert, then Module
> You should see the code window pop up on the right hand side
>
> Paste the code in there.
>
> Don't forget to fix the range to fix:
> wks.Range("a1,d1,f1").......
>
> Now go back to excel and save this workbook (so you don't have to do this
> portion again).
>
>
> Then test it out:
>
> Open one of those workbooks that has a worksheet to fix.
> Select one of those worksheets
>
> tools|macro|macros...
> select the macro and click run.
>
> J. Gutierrez wrote:
> >
> > Thanks for the reply....I've listed the numbers 000-999 and their replacement
> > strings in columns A and B, but where do I enter the code for the macro?
> >
> > Thanks,
> > JG
> >
> > "Dave Peterson" wrote:
> > > Create a new workbook.
> > > Put the values 000 to 900 in column A.
> > > If those values are brought in as 0, 1, ..., 900 in the .CSV file, and you give
> > > them a custom format of 000, then do the same thing in this column.
> > >
> > > Put the strings that do the replacements in column B.
> > >
> > > (Yep, you'll have a macro that just does the edit|replace 900 times.)
> > >
> > > Then put this code into a general module of that same workbook.
> > >
> > > Option Explicit
> > > Sub DoLotsOfChanges()
> > >
> > > Dim wks As Worksheet
> > > Dim tableWks As Worksheet
> > > Dim myCell As Range
> > > Dim myRng As Range
> > >
> > > Set wks = ActiveSheet
> > > Set tableWks = ThisWorkbook.Worksheets("sheet1")
> > >
> > > With tableWks
> > > Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
> > > End With
> > >
> > >
> > > For Each myCell In myRng.Cells
> > > wks.Range("a1,d1,f1").EntireColumn.Replace what:=myCell.Value, _
> > > replacement:=myCell.Offset(0, 1).Value, _
> > > lookat:=xlWhole, searchorder:=xlByRows, _
> > > MatchCase:=False
> > > Next myCell
> > >
> > > End Sub
> > >
> > > Adjust this line:
> > > wks.Range("a1,d1,f1")
> > > to match the columns that need the "fixing".
> > >
> > > Now save this workbook as a nice name.
> > >
> > > when you get the next .csv file...
> > > Open the workbook with the code and table.
> > > then open the .csv file.
> > >
> > > Make sure you're on the worksheet that needs fixing.
> > >
> > > Tools|Macro|macros|
> > > click on DoLotsOfChanges
> > > and click run.
> > >
> > > If you're new to macros, you may want to read David McRitchie's intro at:
> > > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> > >
> > >
> > >
> > > J. Gutierrez wrote:
> > > >
> > > > OK, let me briefly explain...I work in a medical research lab, and part of my
> > > > job is organizing excel files containing patient medical info. The data is
> > > > imported as a .csv file from a large cancer registry. There are columns of
> > > > data where a three digit number represents text. (example: 000 is heart, 001
> > > > is lung, etc.) In order to be able to work with the data, I have to replace
> > > > the numbers with the corresponding text. (The values all come out of a
> > > > standardized reference) Currently, I have to do this manually, using the Find
> > > > and Replace window in excel. With over 900 values to replace, it gets
> > > > tedious, especially when I have to repeat the whole process for every project
> > > > that we start.
> > > >
> > > > There must be a way to automatically make all of the replacements in the
> > > > spreadsheet.....any suggestions?
> > > >
> > > > Thanks in advance for any help.
> > > >
> > > > JG
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

You're welcome and I hope everything is ok.

J. Gutierrez wrote:
>
> Meant to say "Thank you"...got interrupted by the hurricane last week.
>
> Thanks.
>
> "Dave Peterson" wrote:
>
> > If you're new to macros, you may want to read David McRitchie's intro at:
> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >
> > Short course:
> >
> > Start a new workbook (so you can use it against any other workbook)
> >
> > Hit alt-f11 to get to the VBE (where macros/UDF's live)
> > hit ctrl-R to view the project explorer
> > Find your workbook.
> > should look like: VBAProject (book1)
> >
> > right click on the project name
> > Insert, then Module
> > You should see the code window pop up on the right hand side
> >
> > Paste the code in there.
> >
> > Don't forget to fix the range to fix:
> > wks.Range("a1,d1,f1").......
> >
> > Now go back to excel and save this workbook (so you don't have to do this
> > portion again).
> >
> >
> > Then test it out:
> >
> > Open one of those workbooks that has a worksheet to fix.
> > Select one of those worksheets
> >
> > tools|macro|macros...
> > select the macro and click run.
> >
> > J. Gutierrez wrote:
> > >
> > > Thanks for the reply....I've listed the numbers 000-999 and their replacement
> > > strings in columns A and B, but where do I enter the code for the macro?
> > >
> > > Thanks,
> > > JG
> > >
> > > "Dave Peterson" wrote:
> > > > Create a new workbook.
> > > > Put the values 000 to 900 in column A.
> > > > If those values are brought in as 0, 1, ..., 900 in the .CSV file, and you give
> > > > them a custom format of 000, then do the same thing in this column.
> > > >
> > > > Put the strings that do the replacements in column B.
> > > >
> > > > (Yep, you'll have a macro that just does the edit|replace 900 times.)
> > > >
> > > > Then put this code into a general module of that same workbook.
> > > >
> > > > Option Explicit
> > > > Sub DoLotsOfChanges()
> > > >
> > > > Dim wks As Worksheet
> > > > Dim tableWks As Worksheet
> > > > Dim myCell As Range
> > > > Dim myRng As Range
> > > >
> > > > Set wks = ActiveSheet
> > > > Set tableWks = ThisWorkbook.Worksheets("sheet1")
> > > >
> > > > With tableWks
> > > > Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
> > > > End With
> > > >
> > > >
> > > > For Each myCell In myRng.Cells
> > > > wks.Range("a1,d1,f1").EntireColumn.Replace what:=myCell.Value, _
> > > > replacement:=myCell.Offset(0, 1).Value, _
> > > > lookat:=xlWhole, searchorder:=xlByRows, _
> > > > MatchCase:=False
> > > > Next myCell
> > > >
> > > > End Sub
> > > >
> > > > Adjust this line:
> > > > wks.Range("a1,d1,f1")
> > > > to match the columns that need the "fixing".
> > > >
> > > > Now save this workbook as a nice name.
> > > >
> > > > when you get the next .csv file...
> > > > Open the workbook with the code and table.
> > > > then open the .csv file.
> > > >
> > > > Make sure you're on the worksheet that needs fixing.
> > > >
> > > > Tools|Macro|macros|
> > > > click on DoLotsOfChanges
> > > > and click run.
> > > >
> > > > If you're new to macros, you may want to read David McRitchie's intro at:
> > > > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> > > >
> > > >
> > > >
> > > > J. Gutierrez wrote:
> > > > >
> > > > > OK, let me briefly explain...I work in a medical research lab, and part of my
> > > > > job is organizing excel files containing patient medical info. The data is
> > > > > imported as a .csv file from a large cancer registry. There are columns of
> > > > > data where a three digit number represents text. (example: 000 is heart, 001
> > > > > is lung, etc.) In order to be able to work with the data, I have to replace
> > > > > the numbers with the corresponding text. (The values all come out of a
> > > > > standardized reference) Currently, I have to do this manually, using the Find
> > > > > and Replace window in excel. With over 900 values to replace, it gets
> > > > > tedious, especially when I have to repeat the whole process for every project
> > > > > that we start.
> > > > >
> > > > > There must be a way to automatically make all of the replacements in the
> > > > > spreadsheet.....any suggestions?
> > > > >
> > > > > Thanks in advance for any help.
> > > > >
> > > > > JG
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson