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

J

jenniferlawhp

Is there a shortcut to make a cell negative?

I'm looking for a simpler way than "F2, home, -" for each cell o
running a "=0-A1" in another column and then pasting the cells a
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 fo
"make negative"? Oh, it'd be so great if you could select a range o
cells and then do Control-whatever and it would make them all negative
 
S

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
 
J

jenniferlawhp

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

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
 
D

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
 
H

Harlan Grove

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

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
Click to expand...

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
Click to expand...

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
 
D

Dave Peterson

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

Harlan Grove

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

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