Going from debit to credit

[Guest]

This is a simplistic question, but I'll be hanged if I can find a solution!
At times, I want to do a copy/paste. That I can do, no problem. But what I
really ALSO want to do, at the same time, is tell the spreadsheet that when
you paste these positive numbers from here to there, I want you to convert
all of these positive numbers to negative numbers while you're at it.

It's a time-consuming pain in the a@@ to have to sit there and hit F2
constantly, so I can pull up that positive number and put a minus sign at
the front (the first digit) of it. Is there someway to instantly convert the
actual values from positive to negative?

Note: Office 2007 doesn’t seem to be any better at this than Office 2003.
You’d think by now, Microsoft would have figured out that some of us
beancounters (and there are a ton of us out here!) might be into this feature.

Thanks,
Cleveland331
This is a simplistic question, but I'll be hanged if I can find a solution!
At times, I want to do a simply copy/paste. That I can do, no problem. But
what I really ALSO want to do, at the same time, is tell the spreadsheet that
when you paste these positive numbers from here to there, I want you to
convert all of these positive numbers to negative numbers while you're at it.

It's a time-consuming pain in the a@@ to have to sit there and hit F2
constantly, so I can pull up that positive number and put a negative and the
front (the first digit) of it. Is there someway to instantly convert the
actual values from positive to negative?

Thanks,
Cleveland331

 

[Guest]

You can type -1 in a cell, copy it, select the cells you want to be negative,
and click Paste Special. In the dialog box, check Multiply.

 

[Guest]

another method. if your numbers are in column A, starting with cell A1, enter

=-A1

in cell B1 and copy down as far as necessary. If you need to hardcode the
values in column B, copy column B, then click Edit/Paste Special and select
values.

 

[Tim879]

The fastest way is to put a negative one in any blank cell on the
spreadsheet. Then select the cell where you put the negative 1, hit
copy.

select the range of cells you want to change the sign on and go to
edit -> paste special -> multiply and hit ok. This multiplies the
range you selected by -1 thereby changing the sign.

If you want just numbers to appear (e.g. -100) and not =100*-1, select
the values options as well.

 

[Guest]

I wasn't blaming Microsoft for anything..and I'm pretty damn good at Excel,
thank you. If you didn't know how to help me with this particular question,
you should have just shut up.

 

[Gord Dibben]

Maybe you could adapt this event code to suit.

As written, any number(s) copied into the range of A1:A100 will be changed to
negative if not already so.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A100"
Dim cell As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each cell In Target
If Left(cell.Value, 1) <> "-" Then
cell.Value = cell.Value * -1
End If
Next cell
End If

ws_exit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[Tyro]

I did know the answer of the paste special options to reverse the sign. You
blamed Microsoft