Macro to identify column by header and replace number values with string values

[kang6789]

Hi, I am stuck on a seemingly simple issue within a much larger Excel macro. I need the macro to find the column headed "PRIORITY" (changes with each data download) and recode number values in the column with their corresponding string values. Currently, the column contains 1's, 2's, and 3's. I need to replace those values with high, medium, and low:

1=high
2=medium
3=low

The code below is what I have so far. but it theoretically would only work for the first recoding, 1=high. Moreover, even that doesn't work because Excel sees the "PRIORITY" column as containing number values, not string values (the Macro leaves the cell blank instead of filling it with "high").

Please Login or Register  to view this content.

Can anyone help with this? Thank you.

[jaslake]

Hi kank6789

You'll need to change this line of Code from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

To solve this

it theoretically would only work for the first recoding, 1=high

I'd suggest a Select Case Statement.

[TMS]

You are changing 1 to High ... notice, no quotation marks. Therefore, High is seen as a (undefined) variable and hence has no value. Change it to "High"

Once you know what the range is, why don't you do a global replace instead of looping through the range?

Regards, TMS

[Mumps1]

Try:

Please Login or Register  to view this content.