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

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

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