Back to Negative Numbers

sburkhar

Active Member
Joined
Oct 4, 2006
Messages
363
I'm still looking for a way to make numbers entered in a column appear and function as negetive numbers without having to add the minus sign to every entry.
Is there a way to format the column like this?

I'm code illiterate - so if you have a code for this, please be very specific about where and how to enter this code.

Thank you so much for any help!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

mbb4331

Board Regular
Joined
Dec 22, 2005
Messages
152
You could enter a positive number in A1, then in B1 enter "=-A1".
 

sburkhar

Active Member
Joined
Oct 4, 2006
Messages
363
You could enter a positive number in A1, then in B1 enter "=-A1".

that DOES work, but I can't add any more columns to this massive spreadsheet. But barring any other fixes, I'll continue to see if I can make your suggestion work.

thank you
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
You can convert them permanently like this:

- Open a new workbook

- In any cell enter -1

- Copy that cell

- Go back to your list and select the values

- Select Paste Special and Multiply

They should all change from positive to negative and vice versa.

Hope it helps
 

sburkhar

Active Member
Joined
Oct 4, 2006
Messages
363
You can convert them permanently like this:

- Open a new workbook

- In any cell enter -1

- Copy that cell

- Go back to your list and select the values

- Select Paste Special and Multiply

They should all change from positive to negative and vice versa.

Hope it helps

That is a good "after the fact" choice that I can use too. Thanks!
I was hoping to get the template columns set up to function as negatives immediately upon entering the number. I really thought this was going to be an easy one.
I apologize for all the trouble and thanks to everyone who took the time to answer me.
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Hi again.

I think this code should work.

To make it work for a whole worksheet use this:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Target.Value = Target.Value * -1
End Sub

Or for a specific range use this:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRange As Range, myCell As Range

Set myRange = Range("A1:A10")

On Error GoTo Finish
Application.EnableEvents = False

If Intersect(Target, myRange) Is Nothing Then GoTo Finish

For Each myCell In Target
    Target.Value = Target.Value * -1
Next myCell

Finish: Application.EnableEvents = True

End Sub

Changing the Range("A1:A10") to suit what you require.

You need to right click on your worksheet tab and select View Code and paste it there.

I think it should work although I always warn people I'm a bit of a code novice and have cobbled this together from a few posts so it could be flawed.

One thing you also may need to watch out for is I don't think it will work if people paste the numbers into the cells.
 

Forum statistics

Threads
1,136,345
Messages
5,675,225
Members
419,555
Latest member
Paddington

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top