Can you automatically change positive numbers to negative numbers (and vice versa)

L

Lauren

My brother is working on a worksheet and he needs to change all the
positive numbers to negative numbers and all the negative numbers to
positive numbers. He said that, when he used to use Lotus, there was
a button he could click to automatically do this. Does Excel have a
function like this? He is trying NOT to have to reenter all the
numbers.

Thanks for any help you can provide.
 
J

J.E. McGimpsey

My brother is working on a worksheet and he needs to change all the
positive numbers to negative numbers and all the negative numbers to
positive numbers. He said that, when he used to use Lotus, there was
a button he could click to automatically do this. Does Excel have a
function like this? He is trying NOT to have to reenter all the
numbers.


There's no button, but it's easy to do:

1) Enter -1 in an empty cell

2) Copy the cell

3) Select the numbers to be inverted.

4) Choose Edit/Paste Special, selecting the Values and Multiply
Radio buttons. Click OK.

5) Delete the -1 from the cell in step 1


If it has to be done frequently, it can be done a bit differently
via a macro:


Public Sub Invert()
Dim rNumbers As Range
Dim rCell As Range
On Error Resume Next 'in case no numbers to invert
With Selection
If .Count = 1 Then
Set rNumbers = .Cells
Else
Set rNumbers = Selection.SpecialCells( _
xlCellTypeConstants, xlNumbers)
End If
End With
For Each rCell In rNumbers
rCell.Value = -rCell.Value
Next rCell
End Sub
 
G

Guest

but if you want to change the + to neg on my bills and cc
cards,hey go gor it i wont argue.LOL
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top