How to automate process of replacing values?

[Guest]

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

 

[Guest]

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]

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.

 

[Guest]

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

Thanks.

 

[Dave Peterson]

You're welcome and I hope everything is ok.

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

Thanks.