Wanting to change numbers values in a row to a defined non-currency text value

[jdev33]

My workbook contains two sheets: a master data sheet and a template. When I run a macro it copies the template to a third sheet which is then populated with specified information from the master sheet. One column that is copied is filled with numbers, from 1-4; each number represents a time code(eg. 1 = Full time, 2 = Part time, 3 = Intermittent, 4 = Seasonal). Unfortunately for me, people in a much higher place have decided that it's too difficult to do this conversion in their heads and want the reports that I run to have the text reference in the noted column instead of the number.

I am completely open to any VBA or function suggestions for solving this... conditional formatting, worksheet_change, altering the template, vba code in existing macros, anything. Almost all searches on converting numbers refers to changing number values to words for the numbers rather than some other defined value. Also, I don't want to add anymore columns to the current reports. So there it is; any and all advice on this one is greatly welcomed and appreciated!

 

[Andrew Poulsom]

How are the numbers 1 to 4 getting in the column? Can't you use a lookup table to find the appropriate text?

 

[Rick Rothstein]

You can use this macro to convert your numbers to words...

Sub ChangeTimeCodesToWords()
  Dim X As Long, TimeWords() As String
  TimeWords = Split("Full time,Part time,Intermittent,Seasonal", ",")
  For X = 1 To 4
    Columns("E").Replace X, TimeWords(X - 1), xlWhole
  Next
End Sub

In the above code, I have assumed your Time Codes are in Column E... change that as needed.

 

[jdm21788]

Hard to be specific with an answer, but try vlookup. If you aren't familiar with the function, it will produce an answer by looking up a specified cell (in this case, the number that defines the type of worker) and then referencing a table that you have placed somewhere in the workbook. The table would look something like this:

1 Full-Time
2 Part-Time
3 Intermittent
4 Seasonal

In the cell where you want to see the description rather than the number, you would type:

=vlookup([cell reference of the number],[the range for the table you have created],2)

The 2 tells Excel which column to return from the table your are referencing.

Hopefully this helps!

 

[jdev33]

Rick, that worked out great. I just inserted a line into my pre-existing macros to go run the edited version of that, and it does exactly what I need with little very little time added to the report generation. It just took a little fiddling to find the best spot for inserting the run line, but it works great.

In response to the others, I started with VLOOKUP on this problem, but because of the way the reports are generated I simply could not get it to apply to the entire row. VLOOKUP does work with this for other spreadsheets I use - I just can't use it in this instance.