Reverse sign of range
 

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

Reverse sign of range
 

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

Reverse sign of range
 

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