Reversing sign (+ to -) (- to +)

  • Thread starter Thread starter M Stokes
  • Start date Start date
M

M Stokes

I have frequent need to quickly reverse the sign on all data in a
column from positive to negative or negative to positive.

For example, the column that contains the data I want to reverse sign
on is Column A.

So far my "caveman" approach has been to:
1. add a temporary Column B and copy down -1 for all rows for which
column A has data
2. next create a temporary Column C and copy down the formula Column
A * Column B for all rows for which Column A has data
3. next Copy Column C and do a Paste Special Values over Column A
4. finally, delele temporary Columns B and Column C.

Is there an easier way to do this. Heard you could do using Paste
Special and use the Operation Section. However, when I use muliply it
simply squares the data.
 
M stokes

One way:

Enter -1 in a cell. Copy that cell.
Select the column.
Choose Edit > Paste special and Multiply.
OK and <Esc>. Delete the -1 cell.
 
To reverse the sign, put a -1 in a cell, copy it, select the range to
reverse sign on, edit>paste special>multiply.
 
Here is a sub that will change - to + and + to minus. You may want to make 2
separate subs.

Sub chg1stchar()
For Each c In Selection
If Left(c, 1) = "-" Then
c.Value = Abs(c)
Else
c.Value = "-" & c
End If
Next
End Sub
 
Marvin

Enter -1 in a cell.

Copy this.

Select entire range of cells to change.

Paste Special>Multiply>OK>Esc.

Gord Dibben Excel MVP
 
Back
Top