Subtract from X formula?

[tuix]

Hello everyone, I am in absolute emergency mode right now, freaking out because I think I messed up big time. The issue is that someone sent me data with survey answers ranging between values 1-5. I put them in an excel sheet to make tables. The issue is, in order for the answers to make sense, they needed to be reversed. However I did not know this, and therefore made a whole excel file of 12 sheets and near 200 tables according these values. Now she tells me that they needed to be reversed.

What I need to know is whether there is a way to convert all cell values into what they become after being subtracted from 6. Example;

Currently the cells are:
A1 = 2


A2 = 4.2
A3 = 3.51

What I need them to be:
A1 = 4
A2 = 1.8
A3 = 2.49

Is there a way to do this? I looked around a bit and fiddled with excel on my own but couldn't figure it out. Things like =6-X obviously don't work. This thing is due in a few days so I would really appreciate to know even if there is no way of doing this so I start redoing the data right now. Thanks for all the help.

[Alansidman]

I would do this with VBA. The following code will work if you have all your data in Column A.

Option Explicit

Sub MinusSix()
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 1 To lr    'Assumes data starts in Row 1
        Range("A" & i) = 6 - Range("A" & i)
    Next i
    Application.ScreenUpdating = True

End Sub

[Alansidman]

I would do this with VBA. The following code will work if you have all your data in Column A.

Option Explicit

Sub MinusSix()
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 1 To lr    'Assumes data starts in Row 1
        Range("A" & i) = 6 - Range("A" & i)
    Next i
    Application.ScreenUpdating = True

End Sub

[ArviLaanemets]

1. Into row 1 of some free column enter the formula

=6-$A1

2. Copy the formula down until row with last entry in column A;
3. Copy all cells with formula. Right-click on cell A1, and select Paste Special and then the option Values from dropdown menu;
4. Delete formulas. It's done!

[p45cal]

Another way: Create a new sheet and call it bleugh. In its cell A1 enter 6, and in cell B1 enter -1

Then go to your sheet with the cells that want changing, select the cells and run:

Sub blah()
    Selection.SpecialCells(xlCellTypeConstants, 1).Select' selects only plain numbers within your selection.
    Sheets("bleugh").Range("A1").Copy '6
    Selection.PasteSpecial Operation:=xlSubtract
    Sheets("bleugh").Range("B1").Copy '-1
    Selection.PasteSpecial Operation:=xlMultiply
End Sub