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.

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

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" <jenniferlawhp.1tk32l_1123689917.7853@excelforum-nospam.com>
skrev i melding
news:jenniferlawhp.1tk32l_1123689917.7853@excelforum-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
>