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

[jenniferlawhp]

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.

[swatsp0p]

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 Special>Multiply if you like.

Done!

HTH

Bruce

[jenniferlawhp]

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

[swatsp0p]

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

[Duke Carey]

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
>
>

[Harald Staff]

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
>

[Harlan Grove]

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]

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

[Harlan Grove]

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.