Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Negative column
Hi, I would like to be able to enter a positive number in a cell and Excell
automaticaly make it a negitive. I sure there is an easy answer to this, but I can't figure it out. Any ideas? Mark |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Negative column
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If IsNumeric(.Value) Then If .Value < 0 Then .Value = .Value * -1 End If End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click -- --- HTH Bob (change the xxxx to gmail if mailing direct) "haigh" wrote in message ... Hi, I would like to be able to enter a positive number in a cell and Excell automaticaly make it a negitive. I sure there is an easy answer to this, but I can't figure it out. Any ideas? Mark |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Negative column
You can also enter the list of positive numbers, and, upon completion, enter
-1 somewhere on the worksheet, copy it, select the column of numbers you entered, select paste special, multiply. This will convert all the positive numbers to negative. -- Brevity is the soul of wit. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "H1:H10" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If IsNumeric(.Value) Then If .Value < 0 Then .Value = .Value * -1 End If End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click -- --- HTH Bob (change the xxxx to gmail if mailing direct) "haigh" wrote in message ... Hi, I would like to be able to enter a positive number in a cell and Excell automaticaly make it a negitive. I sure there is an easy answer to this, but I can't figure it out. Any ideas? Mark |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Negative column
This could be an easy answer, copy the data, in a blank section Editpaste
specialSubtract "haigh" wrote: Hi, I would like to be able to enter a positive number in a cell and Excell automaticaly make it a negitive. I sure there is an easy answer to this, but I can't figure it out. Any ideas? Mark |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Negative column
It will also convert negative numbers to positive!
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "Dave F" wrote in message ... You can also enter the list of positive numbers, and, upon completion, enter -1 somewhere on the worksheet, copy it, select the column of numbers you entered, select paste special, multiply. This will convert all the positive numbers to negative. -- Brevity is the soul of wit. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "H1:H10" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If IsNumeric(.Value) Then If .Value < 0 Then .Value = .Value * -1 End If End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click -- --- HTH Bob (change the xxxx to gmail if mailing direct) "haigh" wrote in message ... Hi, I would like to be able to enter a positive number in a cell and Excell automaticaly make it a negitive. I sure there is an easy answer to this, but I can't figure it out. Any ideas? Mark |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Negative column
If Dave F answer almost worked maybe something similar to
=IF(A1<0,A1,A1*"-1") "Dave F" wrote: You can also enter the list of positive numbers, and, upon completion, enter -1 somewhere on the worksheet, copy it, select the column of numbers you entered, select paste special, multiply. This will convert all the positive numbers to negative. -- Brevity is the soul of wit. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "H1:H10" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If IsNumeric(.Value) Then If .Value < 0 Then .Value = .Value * -1 End If End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click -- --- HTH Bob (change the xxxx to gmail if mailing direct) "haigh" wrote in message ... Hi, I would like to be able to enter a positive number in a cell and Excell automaticaly make it a negitive. I sure there is an easy answer to this, but I can't figure it out. Any ideas? Mark |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Negative column
or just
=ABS(A1) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Spidey" wrote in message ... If Dave F answer almost worked maybe something similar to =IF(A1<0,A1,A1*"-1") "Dave F" wrote: You can also enter the list of positive numbers, and, upon completion, enter -1 somewhere on the worksheet, copy it, select the column of numbers you entered, select paste special, multiply. This will convert all the positive numbers to negative. -- Brevity is the soul of wit. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "H1:H10" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If IsNumeric(.Value) Then If .Value < 0 Then .Value = .Value * -1 End If End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click -- --- HTH Bob (change the xxxx to gmail if mailing direct) "haigh" wrote in message ... Hi, I would like to be able to enter a positive number in a cell and Excell automaticaly make it a negitive. I sure there is an easy answer to this, but I can't figure it out. Any ideas? Mark |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Negative column
Thanks all. Have got it working now. Many thanks.
~Mark "Spidey" wrote: This could be an easy answer, copy the data, in a blank section Editpaste specialSubtract "haigh" wrote: Hi, I would like to be able to enter a positive number in a cell and Excell automaticaly make it a negitive. I sure there is an easy answer to this, but I can't figure it out. Any ideas? Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
IF/AND/OR/DATEIF Issue...sorry...long post... | Excel Worksheet Functions | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
match and count words | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions |