Provide your selection is always data (constant numerical values, not
formulas), this is even less code...

Sub ChangeSigns()
Dim Cell As Range
For Each Cell In Selection
Cell.Value = -Cell.Value
Next
End Sub

--
Rick (MVP - Excel)


"Paul" wrote in message
...
OK - saddo that I am - I've found a workaround to this.

I keep my 'useful functions' in one macro file that I have hidden.
I've placed a range in that file with -1 in it. Copied THAT range and
then
proceded as before.

Hey Presto ! Can now reverse the signs of all items in a range with one
click.
Sub Paste_Minus()
Workbooks("Other
Macros.xls").Worksheets("Sheet1").Range("Negative_ Calculator").Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlMultiply,
SkipBlanks _
:=False, Transpose:=False

End Sub

Remarkably, it's a lot less code as well.

"Paul" wrote:

I have a routine that reverses the sign of a list of data. This
particularly
useful when entering a list of items and discovering that they're the
wrong
way round.

However, it relies on cell A1 being available to create the -1 in the
clipboard.
The drawback with this is that it doesn't work on protected sheets
(unless
A1 is unprotected, of course).

Is there a better way of putting -1 into the clipboard ?

Sub Paste_Minus()
' Check that a file is open before attempting to run the procedure
If IsFileOpen() = False Then Exit Sub
' cFormula is the formula contained in cell A1
cFormula = Range("A1").Formula
Range("A1").Value = -1
Range("A1").Copy

Selection.PasteSpecial Paste:=xlValues, Operation:=xlMultiply,
SkipBlanks _
:=False, Transpose:=False

' Return the original formula in A1
Range("A1").Formula = cFormula
End Sub