## 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

Assumption: The 1-5 data starts in A2

- Insert a column (B) to the right of the column with the 1-5 data
- In B2 enter "=CHOOSE(A2,5,4,3,2,1)" or "=VALUE(MID("54321",A2,1))"
- Copy/Fill that formula down
- Copy Column B results and Paste Value into column A
- 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

## No comments:

## Post a Comment