## The Problem

This week's question to Allen at ExcelTips was from a person who wanted to reverse the values in a list of numbers ranging from 1 to 5: 5 becomes 1, 4 becomes 2, 3 stays 3, 2 becomes 4, and 5 becomes 1.  This problem is seen by college admins and high school guidance counselors because in some schools an A is 5 (or 4), in others it is 1.

There are a number of ways to do this.  This is what I submitted to Allen (with some edits, of course).

## The Worksheet Method(s)

First, is the worksheet method, and there are two choices.
Assumption:  The 1-5 data starts in A2
1. Insert a column (B) to the right of the column with the 1-5 data
2. In B2 enter "=CHOOSE(A2,5,4,3,2,1)"  or "=VALUE(MID("54321",A2,1))"
3. Copy/Fill that formula down
4. Copy Column B results and Paste Value into column A
5. Delete column A

## The VBA Subs

The second formula is the basis for the following subroutine after selecting the column

Sub ReverseIntegers()
Dim i As Double
On Error Resume Next ' This allows cells with text, like a header row to be ignored
For i = 1 To Selection.Cells.Count
Selection.Cells(i).Value = CInt(Mid("54321", Selection.Cells(i).Value, 1))
Next i
On Error GoTo 0
End Sub

Adapting the CHOOSE formula directly would result in a Select Case structure but the Instr function could also be used:

Sub ReverseIntegers2()
Dim i As Double
On Error Resume Next ' This allows cells with text, like a header row to be ignored
For i = 1 To Selection.Cells.Count
Selection.Cells(i).Value = CInt(InStr(1, "54321", CStr(Selection.Cells(i).Value)))
Next i
On Error GoTo 0
End Sub