changing positive to negative numbers

[Laura Fraser]

I have data copied from another source that needs to
routinely be changed to show as negative numbers. In
Lotus one could create a Macro to change all numbers in
an area from positive to negative. Any ideas how this can
be done in Excel. Any help will be appreciated.

 

[Dan E]

Put -1 in a cell
Copy that cell
Select the range you want to change signs in
Paste Special & Choose Multiply

Dan E

 

[Dan E]

Here's a macro that will make the selected range change signs

Sub Negatives()
For Each Cell In Selection
Cell.Value = -Cell.Value
Next
End Sub

Dan E

 

[David McRitchie]

Hi Laura,
you could use On Error Resume Next
in case there were any non numeric constants

If they are only going to be constants and never formulas
which Dan's macro would have destroyed anyway you might use

Sub ChangeSign()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
On Error Resume Next
For Each cell In Selection.Cells.SpecialCells(xlConstants, xlNumbers)
cell.Value = -cell.value
next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

instructions for installing / using a macro on my
http://www.mvps.org/dmcritchie/excel/getstarte.htm
--

 

[Dan E]

I have data copied from another source that needs to
Not often imported data contains formulas to "destroy"???
The On Error Resume Next isn't a bad idea though.

Dan E