Replace or recode several different values by a single value in an Excel file

Question

Hi all,

I have an Excel worksheet which contains data in several columns. For a specific column, I will need Excel to replace all values between, say 10 to 15, by the value 1 and values between 16 and 20 by the value 2 and so forth.

I know how to do it for a single value; ie: I can replace value 10 by 1, 11 by 1 and so on. But this will be a tedious exercise. Are there some lines of codes that can execute this task?

Thanks for your help.

regards, Ali

Answer 1

Here's a quick and dirty way, that will have to be modified based on what values you really want to change to and from. This converts 11 to 15 to 1, and 16 to 20 to 2, etc.

You'll probably want to put a button on the spreadsheet, and then right click and choose "View Code". Then enter the following between the Sub and End Sub statement. "RangetoChange" is the name of a named range, OR you can put in the range itself, like "A1:A100". Once it's all entered, then you just get out of design mode (that you entered to place the button), then click the button. Make sure that you change the formula to what you really want the result to be.

Dim A() As Variant
Dim i As Integer

A = Range("RangetoChange")
For i = 1 To (UBound(A) - LBound(A) + 1)
    A(i, 1) = (A(i, 1) \ 5) - 1
Next i
Range("RangetoChange") = A

Answer 2

Hi Lance,

Thanks for the reply. I'm a total nerd as far as macros are concerned! I just copied your lines of code and pasted them in the VB editor but when I click the RUN command, I get a message "Invalid outside procedure" with the "RangeToChange" being highlighted in the codes window. What am I doing wrong?

rgds, Ali

Answer 3

If you do not need to automate this with a macro or repeat often, you can do this quickly right on the spreadsheet.

1. Insert a new column to the right of the column to change. Let's say the column was A and you just created a new column B.
2. Enter the formula =INT(A1/5) - 1 in cell B1.
3. Copy this formula to all cells in column B for the extent of the cells in column A.
4. Copy the cells in column B and paste to column A using right-click, "Paste special...", Values, then click Ok.
5. Delete column B (this is a temporary/work column).

This will replace the values in column A with the adjusted values in column B. Proceed with any other columns.