Is there NOT a "make negative" function? and if not WHY???????
 

Is there a shortcut to make a cell negative?

I'm looking for a simpler way than "F2, home, -" for each cell or
running a "=0-A1" in another column and then pasting the cells as
values and deleting the column with the formula.

Grrrrrr! It seems there ought to be a simpler way to do it.

Control-D copies the cell above it... is there something like that for
"make negative"? Oh, it'd be so great if you could select a range of
cells and then do Control-whatever and it would make them all negative.


--
jenniferlawhp
------------------------------------------------------------------------
jenniferlawhp's Profile: http://www.excelforum.com/member.php...o&userid=26137
View this thread: http://www.excelforum.com/showthread...hreadid=394637

This isn't exactly what you wanted, but it is sure easier than "F2,
home, -" ...

In an unused cell, enter "-1" (no quotes) and copy this cell.

Highlight the range to make negative and Paste Special and select
Multiply

Note: You can use Control-Clicks to select non-adjoining cells prior
to doing the Paste SpecialMultiply if you like.

Done!

HTH

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=394637

It's not as simple as I'd like it to be, but that'll work better! Thank
you thank you thank you!


--
jenniferlawhp
------------------------------------------------------------------------
jenniferlawhp's Profile: http://www.excelforum.com/member.php...o&userid=26137
View this thread: http://www.excelforum.com/showthread...hreadid=394637

I'm sure if someone knows of a better way, they will share with us. In
the meantime, I am glad that this will make your task somewhat easier.
Thanks for the feedback, it is always appreciated!

Cheers!

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=394637

If this is something you do quite a bit, you can put this code in your
personal.xls file & assign it to a toolbar button.

NOTE: I've put no error checking in this code
DOUBLE NOTE: One of the MVPs here could probably streamline this code quite
a bit



Sub MakeValuesNegative()
Dim rngTgt As Range
Dim rngNegOne As Range
Dim intRows As Long

Application.ScreenUpdating = False
Set rngTgt = Selection
With ActiveSheet.UsedRange
intRows = .Rows.Count
Set rngNegOne = .Offset(intRows, 0).Resize(1, 1)
End With
rngNegOne = -1
rngNegOne.Copy
rngTgt.PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply
rngNegOne.Clear
End Sub




"jenniferlawhp" wrote:


It's not as simple as I'd like it to be, but that'll work better! Thank
you thank you thank you!


--
jenniferlawhp
------------------------------------------------------------------------
jenniferlawhp's Profile: http://www.excelforum.com/member.php...o&userid=26137
View this thread: http://www.excelforum.com/showthread...hreadid=394637

Those things are extremely simple with small macros. Do you know how to use
a macro? Implement a macro? Write a macro?


"jenniferlawhp"
skrev i melding
news:jenniferlawhp.1tk32l_1123689917.7853@excelfor um-nospam.com...

It's not as simple as I'd like it to be, but that'll work better! Thank
you thank you thank you!


--
jenniferlawhp
------------------------------------------------------------------------
jenniferlawhp's Profile:
http://www.excelforum.com/member.php...o&userid=26137
View this thread: http://www.excelforum.com/showthread...hreadid=394637

Duke Carey wrote...
....
DOUBLE NOTE: One of the MVPs here could probably streamline
this code quite a bit

MVPs aren't the only one who could streamline this (and remove
bugs).

Sub MakeValuesNegative()
Dim rngTgt As Range
Dim rngNegOne As Range
Dim intRows As Long

Application.ScreenUpdating = False
Set rngTgt = Selection
With ActiveSheet.UsedRange
intRows = .Rows.Count
Set rngNegOne = .Offset(intRows, 0).Resize(1, 1)
End With

You're making the unfounded assumption that UserRange always
begins in row 1. Open a new workbook and enter XYZ in cell
F10. Call up the VB Editor and check what that worksheet's
UsedRange is.

rngNegOne = -1
rngNegOne.Copy
rngTgt.PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply
rngNegOne.Clear
End Sub

Why screw around with the entire selection if you're only
going to change a single cell. Why not change only the
ActiveCell? Why screw around with Copy and PasteSpecial?


Sub foo()
'overengineered
Dim c As Range, r As Range, s As Boolean

On Error GoTo CleanUp
s = Application.EnableEvents
Application.EnableEvents = False

Set r = Selection.SpecialCells( _
Type:=xlCellTypeConstants, Value:=xlNumbers)

For Each c In r.Cells
c.Value = -c.Value
Next c

CleanUp:
Application.EnableEvents = s

End Sub

But he used:
With activesheet.usedrange
so he was ok with .offset(introws,0).resize(1,1)



Harlan Grove wrote:

Duke Carey wrote...
...
DOUBLE NOTE: One of the MVPs here could probably streamline
this code quite a bit

MVPs aren't the only one who could streamline this (and remove
bugs).

Sub MakeValuesNegative()
Dim rngTgt As Range
Dim rngNegOne As Range
Dim intRows As Long

Application.ScreenUpdating = False
Set rngTgt = Selection
With ActiveSheet.UsedRange
intRows = .Rows.Count
Set rngNegOne = .Offset(intRows, 0).Resize(1, 1)
End With

You're making the unfounded assumption that UserRange always
begins in row 1. Open a new workbook and enter XYZ in cell
F10. Call up the VB Editor and check what that worksheet's
UsedRange is.

rngNegOne = -1
rngNegOne.Copy
rngTgt.PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply
rngNegOne.Clear
End Sub

Why screw around with the entire selection if you're only
going to change a single cell. Why not change only the
ActiveCell? Why screw around with Copy and PasteSpecial?

Sub foo()
'overengineered
Dim c As Range, r As Range, s As Boolean

On Error GoTo CleanUp
s = Application.EnableEvents
Application.EnableEvents = False

Set r = Selection.SpecialCells( _
Type:=xlCellTypeConstants, Value:=xlNumbers)

For Each c In r.Cells
c.Value = -c.Value
Next c

CleanUp:
Application.EnableEvents = s

End Sub

--

Dave Peterson

Dave Peterson wrote...
But he used:
With activesheet.usedrange
so he was ok with .offset(introws,0).resize(1,1)
....

You're right. Sorry Duke.

However, it's still better to operate only on numeric constants. While
the PasteSpecial operation wouldn't change cells containing text,
boolean or error constants, it'd screw up cells containing formulas and
convert blank cells to cells containing the numeric constant zero.