Negative Values in Excel
 

Is it possible to have a series of cells automatically add the negative sign
in front of an inputed number?

Negative Values in Excel
 

Either by formula:

=-A1

or using paste special:
Type -1 in a cell, copy the cell, select the range, use Paste Special,
Operation Multiplication, to multiply the range by -1.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"jlojones821" wrote in message
...
Is it possible to have a series of cells automatically add the negative
sign
in front of an inputed number?

Negative Values in Excel
 

This is a modification of one of Bernie Deitrick's replies earlier this week

Copy the code below, right-click the sheet tab, select "View Code" and paste
the code into the
window that appears.

Change the
Set rngPCode = Range("D:D")
to the column where the numbers to be negated (!!) will be entered.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngPCode As Range
Set rngPCode = Range("D:D")
If Intersect(rngPCode, Target) Is Nothing Then Exit Sub
If Target.Cells.Count 1 Then Exit Sub
On Error GoTo Reset
Application.EnableEvents = False
If (Mid(Target.Value, 1, 1) = "-") Then
Target.Value = Target.Value
Else
Target.Value = Target.Value * -1
End If
Reset:
Application.EnableEvents = True
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"jlojones821" wrote in message
...
Is it possible to have a series of cells automatically add the negative
sign
in front of an inputed number?

Negative Values in Excel
 

We can streamline that code a little bit...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or _
Intersect(Range("D:D"), Target) Is Nothing Then Exit Sub
On Error GoTo Reset
Application.EnableEvents = False
If Left(Target.Value, 1) < "-" Then
Target.Value = -Target.Value
End If
Reset:
Application.EnableEvents = True
End Sub

Rick


"Bernard Liengme" wrote in message
...
This is a modification of one of Bernie Deitrick's replies earlier this
week

Copy the code below, right-click the sheet tab, select "View Code" and
paste the code into the
window that appears.

Change the
Set rngPCode = Range("D:D")
to the column where the numbers to be negated (!!) will be entered.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngPCode As Range
Set rngPCode = Range("D:D")
If Intersect(rngPCode, Target) Is Nothing Then Exit Sub
If Target.Cells.Count 1 Then Exit Sub
On Error GoTo Reset
Application.EnableEvents = False
If (Mid(Target.Value, 1, 1) = "-") Then
Target.Value = Target.Value
Else
Target.Value = Target.Value * -1
End If
Reset:
Application.EnableEvents = True
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"jlojones821" wrote in message
...
Is it possible to have a series of cells automatically add the negative
sign
in front of an inputed number?

Negative Values in Excel
 

I was being lazy!
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Rick Rothstein (MVP - VB)" wrote in
message ...
We can streamline that code a little bit...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or _
Intersect(Range("D:D"), Target) Is Nothing Then Exit Sub
On Error GoTo Reset
Application.EnableEvents = False
If Left(Target.Value, 1) < "-" Then
Target.Value = -Target.Value
End If
Reset:
Application.EnableEvents = True
End Sub

Rick


"Bernard Liengme" wrote in message
...
This is a modification of one of Bernie Deitrick's replies earlier this
week

Copy the code below, right-click the sheet tab, select "View Code" and
paste the code into the
window that appears.

Change the
Set rngPCode = Range("D:D")
to the column where the numbers to be negated (!!) will be entered.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngPCode As Range
Set rngPCode = Range("D:D")
If Intersect(rngPCode, Target) Is Nothing Then Exit Sub
If Target.Cells.Count 1 Then Exit Sub
On Error GoTo Reset
Application.EnableEvents = False
If (Mid(Target.Value, 1, 1) = "-") Then
Target.Value = Target.Value
Else
Target.Value = Target.Value * -1
End If
Reset:
Application.EnableEvents = True
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"jlojones821" wrote in message
...
Is it possible to have a series of cells automatically add the negative
sign
in front of an inputed number?

Negative Values in Excel
 

LOL... Understood (I've been guilty of that myself plenty of times).

Rick


"Bernard Liengme" wrote in message
...
I was being lazy!
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Rick Rothstein (MVP - VB)" wrote in
message ...
We can streamline that code a little bit...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or _
Intersect(Range("D:D"), Target) Is Nothing Then Exit Sub
On Error GoTo Reset
Application.EnableEvents = False
If Left(Target.Value, 1) < "-" Then
Target.Value = -Target.Value
End If
Reset:
Application.EnableEvents = True
End Sub

Rick


"Bernard Liengme" wrote in message
...
This is a modification of one of Bernie Deitrick's replies earlier this
week

Copy the code below, right-click the sheet tab, select "View Code" and
paste the code into the
window that appears.

Change the
Set rngPCode = Range("D:D")
to the column where the numbers to be negated (!!) will be entered.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngPCode As Range
Set rngPCode = Range("D:D")
If Intersect(rngPCode, Target) Is Nothing Then Exit Sub
If Target.Cells.Count 1 Then Exit Sub
On Error GoTo Reset
Application.EnableEvents = False
If (Mid(Target.Value, 1, 1) = "-") Then
Target.Value = Target.Value
Else
Target.Value = Target.Value * -1
End If
Reset:
Application.EnableEvents = True
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"jlojones821" wrote in message
...
Is it possible to have a series of cells automatically add the negative
sign
in front of an inputed number?