Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert neg. numbers to positive numbers
I want to convert neg. numbers to positive numbers. To be clear, I don't
want them just to DISPLAY as positive but to BECOME positive. As well, I don't want to have to had another column (the ABS() function). Thanks to any who can help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert neg. numbers to positive numbers
How about.
Sub makenegpos() For Each c In Range("r1:r9") If c.Value < 0 Then c.Value = -c Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Michaelcip" wrote in message ... I want to convert neg. numbers to positive numbers. To be clear, I don't want them just to DISPLAY as positive but to BECOME positive. As well, I don't want to have to had another column (the ABS() function). Thanks to any who can help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert neg. numbers to positive numbers
If they are all negative, then put -1 in a spare cell and copy that cell.
Then select the numbers and EditPasteSpecialMultiply OK out and clear out the -1. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Michaelcip" wrote in message ... I want to convert neg. numbers to positive numbers. To be clear, I don't want them just to DISPLAY as positive but to BECOME positive. As well, I don't want to have to had another column (the ABS() function). Thanks to any who can help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert neg. numbers to positive numbers
If they are all negative put -1 in an empty helper cell and copy it
select all the negatives and paste special multiply delete the helper cell "Michaelcip" wrote: I want to convert neg. numbers to positive numbers. To be clear, I don't want them just to DISPLAY as positive but to BECOME positive. As well, I don't want to have to had another column (the ABS() function). Thanks to any who can help. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert neg. numbers to positive numbers
Don,
I like your example to change Neg to Pos. How do you change Pos to Neg? I tired the following & it didn't work. I changed only the if statement. Sub MakePosNeg() For Each c In Range("A1:ZZ10000") If c.Value 0 Then c.Value = c Next c End Sub -- Thanks, Kevin "Don Guillett" wrote: How about. Sub makenegpos() For Each c In Range("r1:r9") If c.Value < 0 Then c.Value = -c Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Michaelcip" wrote in message ... I want to convert neg. numbers to positive numbers. To be clear, I don't want them just to DISPLAY as positive but to BECOME positive. As well, I don't want to have to had another column (the ABS() function). Thanks to any who can help. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert neg. numbers to positive numbers
Try Don's original suggestion.
-c will convert positive to negative and negative to positive. AFSSkier wrote: Don, I like your example to change Neg to Pos. How do you change Pos to Neg? I tired the following & it didn't work. I changed only the if statement. Sub MakePosNeg() For Each c In Range("A1:ZZ10000") If c.Value 0 Then c.Value = c Next c End Sub -- Thanks, Kevin "Don Guillett" wrote: How about. Sub makenegpos() For Each c In Range("r1:r9") If c.Value < 0 Then c.Value = -c Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Michaelcip" wrote in message ... I want to convert neg. numbers to positive numbers. To be clear, I don't want them just to DISPLAY as positive but to BECOME positive. As well, I don't want to have to had another column (the ABS() function). Thanks to any who can help. -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert neg. numbers to positive numbers
Dave,
The neg to pos work great. However, I get the following error when I change it to 0. Run-time error '13': Type mismatch. It doesn't like c.Value = -c -- Thanks, Kevin "Dave Peterson" wrote: Try Don's original suggestion. -c will convert positive to negative and negative to positive. AFSSkier wrote: Don, I like your example to change Neg to Pos. How do you change Pos to Neg? I tired the following & it didn't work. I changed only the if statement. Sub MakePosNeg() For Each c In Range("A1:ZZ10000") If c.Value 0 Then c.Value = c Next c End Sub -- Thanks, Kevin "Don Guillett" wrote: How about. Sub makenegpos() For Each c In Range("r1:r9") If c.Value < 0 Then c.Value = -c Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Michaelcip" wrote in message ... I want to convert neg. numbers to positive numbers. To be clear, I don't want them just to DISPLAY as positive but to BECOME positive. As well, I don't want to have to had another column (the ABS() function). Thanks to any who can help. -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert neg. numbers to positive numbers
I bet that the cell that caused the trouble wasn't a cell that contained a
number. So you can add a check: Sub MakePosNeg() For Each c In Range("A1:ZZ10000") if isnumeric(c.value) then If c.Value 0 Then c.Value = -c.value end if end if Next c End Sub I like the block style "if/end if". I changed Don's original suggestion, but it won't matter. AFSSkier wrote: Dave, The neg to pos work great. However, I get the following error when I change it to 0. Run-time error '13': Type mismatch. It doesn't like c.Value = -c -- Thanks, Kevin "Dave Peterson" wrote: Try Don's original suggestion. -c will convert positive to negative and negative to positive. AFSSkier wrote: Don, I like your example to change Neg to Pos. How do you change Pos to Neg? I tired the following & it didn't work. I changed only the if statement. Sub MakePosNeg() For Each c In Range("A1:ZZ10000") If c.Value 0 Then c.Value = c Next c End Sub -- Thanks, Kevin "Don Guillett" wrote: How about. Sub makenegpos() For Each c In Range("r1:r9") If c.Value < 0 Then c.Value = -c Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Michaelcip" wrote in message ... I want to convert neg. numbers to positive numbers. To be clear, I don't want them just to DISPLAY as positive but to BECOME positive. As well, I don't want to have to had another column (the ABS() function). Thanks to any who can help. -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert neg. numbers to positive numbers
Dave,
You are correct; the generic range does include text. Your suggestion works. However because A1:ZZ10000 is a generic range, all non-numeric fields change. Is there a way to preselect a range (for example E6:M100 or G2:K2000) before running the generic macro to change pos to neg? I realize it's safer to go from neg to pos with a generic macro. But we have some instances where we need to fix data entered wrong (chg pos to neg). -- Thanks, Kevin "Dave Peterson" wrote: I bet that the cell that caused the trouble wasn't a cell that contained a number. So you can add a check: Sub MakePosNeg() For Each c In Range("A1:ZZ10000") if isnumeric(c.value) then If c.Value 0 Then c.Value = -c.value end if end if Next c End Sub I like the block style "if/end if". I changed Don's original suggestion, but it won't matter. AFSSkier wrote: Dave, The neg to pos work great. However, I get the following error when I change it to 0. Run-time error '13': Type mismatch. It doesn't like c.Value = -c -- Thanks, Kevin "Dave Peterson" wrote: Try Don's original suggestion. -c will convert positive to negative and negative to positive. AFSSkier wrote: Don, I like your example to change Neg to Pos. How do you change Pos to Neg? I tired the following & it didn't work. I changed only the if statement. Sub MakePosNeg() For Each c In Range("A1:ZZ10000") If c.Value 0 Then c.Value = c Next c End Sub -- Thanks, Kevin "Don Guillett" wrote: How about. Sub makenegpos() For Each c In Range("r1:r9") If c.Value < 0 Then c.Value = -c Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Michaelcip" wrote in message ... I want to convert neg. numbers to positive numbers. To be clear, I don't want them just to DISPLAY as positive but to BECOME positive. As well, I don't want to have to had another column (the ABS() function). Thanks to any who can help. -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert neg. numbers to positive numbers
I'd use something like:
Option Explicit Sub MakePosNeg() Dim myCell As Range Dim Rng As Range Set Rng = Nothing On Error Resume Next Set Rng = Intersect(Selection, _ Selection.Cells.SpecialCells(xlCellTypeConstants, xlNumbers)) On Error GoTo 0 If Rng Is Nothing Then MsgBox "No constant numbers found in selection." Exit Sub End If For Each myCell In Rng.Cells If myCell.Value 0 Then myCell.Value = -myCell.Value End If Next myCell End Sub That .specialcells stuff does the same thing as selecting a larger range and hitting F5 (or Edit|Goto in xl2003 menus). Then Special, then Constants and Numbers (and unchecking all those other options. That means you don't have to be as careful selecting the range and it'll be a little faster--it'll have fewer cells to loop through (usually). AFSSkier wrote: Dave, You are correct; the generic range does include text. Your suggestion works. However because A1:ZZ10000 is a generic range, all non-numeric fields change. Is there a way to preselect a range (for example E6:M100 or G2:K2000) before running the generic macro to change pos to neg? I realize it's safer to go from neg to pos with a generic macro. But we have some instances where we need to fix data entered wrong (chg pos to neg). -- Thanks, Kevin "Dave Peterson" wrote: I bet that the cell that caused the trouble wasn't a cell that contained a number. So you can add a check: Sub MakePosNeg() For Each c In Range("A1:ZZ10000") if isnumeric(c.value) then If c.Value 0 Then c.Value = -c.value end if end if Next c End Sub I like the block style "if/end if". I changed Don's original suggestion, but it won't matter. AFSSkier wrote: Dave, The neg to pos work great. However, I get the following error when I change it to 0. Run-time error '13': Type mismatch. It doesn't like c.Value = -c -- Thanks, Kevin "Dave Peterson" wrote: Try Don's original suggestion. -c will convert positive to negative and negative to positive. AFSSkier wrote: Don, I like your example to change Neg to Pos. How do you change Pos to Neg? I tired the following & it didn't work. I changed only the if statement. Sub MakePosNeg() For Each c In Range("A1:ZZ10000") If c.Value 0 Then c.Value = c Next c End Sub -- Thanks, Kevin "Don Guillett" wrote: How about. Sub makenegpos() For Each c In Range("r1:r9") If c.Value < 0 Then c.Value = -c Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Michaelcip" wrote in message ... I want to convert neg. numbers to positive numbers. To be clear, I don't want them just to DISPLAY as positive but to BECOME positive. As well, I don't want to have to had another column (the ABS() function). Thanks to any who can help. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert neg. numbers to positive numbers
Awesome Dave! You're the man! It also works for better for the Neg to Pos,
by changing the Value to < 0. Thank you very much for your time, Kevin "Dave Peterson" wrote: I'd use something like: Option Explicit Sub MakePosNeg() Dim myCell As Range Dim Rng As Range Set Rng = Nothing On Error Resume Next Set Rng = Intersect(Selection, _ Selection.Cells.SpecialCells(xlCellTypeConstants, xlNumbers)) On Error GoTo 0 If Rng Is Nothing Then MsgBox "No constant numbers found in selection." Exit Sub End If For Each myCell In Rng.Cells If myCell.Value 0 Then myCell.Value = -myCell.Value End If Next myCell End Sub That .specialcells stuff does the same thing as selecting a larger range and hitting F5 (or Edit|Goto in xl2003 menus). Then Special, then Constants and Numbers (and unchecking all those other options. That means you don't have to be as careful selecting the range and it'll be a little faster--it'll have fewer cells to loop through (usually). AFSSkier wrote: Dave, You are correct; the generic range does include text. Your suggestion works. However because A1:ZZ10000 is a generic range, all non-numeric fields change. Is there a way to preselect a range (for example E6:M100 or G2:K2000) before running the generic macro to change pos to neg? I realize it's safer to go from neg to pos with a generic macro. But we have some instances where we need to fix data entered wrong (chg pos to neg). -- Thanks, Kevin "Dave Peterson" wrote: I bet that the cell that caused the trouble wasn't a cell that contained a number. So you can add a check: Sub MakePosNeg() For Each c In Range("A1:ZZ10000") if isnumeric(c.value) then If c.Value 0 Then c.Value = -c.value end if end if Next c End Sub I like the block style "if/end if". I changed Don's original suggestion, but it won't matter. AFSSkier wrote: Dave, The neg to pos work great. However, I get the following error when I change it to 0. Run-time error '13': Type mismatch. It doesn't like c.Value = -c -- Thanks, Kevin "Dave Peterson" wrote: Try Don's original suggestion. -c will convert positive to negative and negative to positive. AFSSkier wrote: Don, I like your example to change Neg to Pos. How do you change Pos to Neg? I tired the following & it didn't work. I changed only the if statement. Sub MakePosNeg() For Each c In Range("A1:ZZ10000") If c.Value 0 Then c.Value = c Next c End Sub -- Thanks, Kevin "Don Guillett" wrote: How about. Sub makenegpos() For Each c In Range("r1:r9") If c.Value < 0 Then c.Value = -c Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Michaelcip" wrote in message ... I want to convert neg. numbers to positive numbers. To be clear, I don't want them just to DISPLAY as positive but to BECOME positive. As well, I don't want to have to had another column (the ABS() function). Thanks to any who can help. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 : Convert Positive Numbers to Negative Numbers ? | Excel Discussion (Misc queries) | |||
change 2000 cells (negative numbers) into positive numbers | Excel Worksheet Functions | |||
convert numbers to positive and keep delta value | Excel Discussion (Misc queries) | |||
Convert a column of numbers from positive to negative in Excel | Excel Discussion (Misc queries) | |||
convert negative numbers to positive numbers and vice versa | Excel Worksheet Functions |