Negative Values in Excel

  • Thread starter Thread starter jlojones821
  • Start date Start date
J

jlojones821

Is it possible to have a series of cells automatically add the negative sign
in front of an inputed number?
 
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
 
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
 
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
 
LOL... Understood (I've been guilty of that myself plenty of times).

Rick
 
Back
Top