AUTOMATICALLY ENTER AND CALCULATE NUMBERS AS NEGATIVES

[EarthAngel]

I used custom format so that I could automatically enter several negative
numbers without keying in the minus sign each time. However the column did
not calculate correctly. What else can I try.

 

[Eduardo]

Hi,
with custom format you will see the numbers as negatives, in your formulas
you will need to treat them as positives for example if you enter 1 in cell
A1 and you format it to be seen as -1, then in B1 you enter =A1 it will show
as 1 (positive) if you want to be shown as negative you have to enter =-A1

 

[Gord Dibben]

Formatting is visual only.

Your data are still positive.

You must use the minus sign or event code.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10" '<<<adjust to suit
Dim cell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = .Value * -1
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code" Copy/paste the code into that
sheet module.

Edit the range to suit and Alt + q to return to Excel.


Gord Dibben MS Excel MVP

 

[EarthAngel]

Thanks for your response. I figured as much. I wanted to see if there was
something out there that I was unaware of that would allow me to use the sum
function instead of creating a formula. I will now fix my formula at the
bottom of the column to allow for this.