Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,124
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default 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   Report Post  
Posted to microsoft.public.excel.misc
bj bj is offline
external usenet poster
 
Posts: 1,397
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 94
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 94
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 94
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 94
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2002 : Convert Positive Numbers to Negative Numbers ? Mr. Low Excel Discussion (Misc queries) 2 November 6th 06 04:30 PM
change 2000 cells (negative numbers) into positive numbers lisbern Excel Worksheet Functions 2 August 16th 06 05:54 PM
convert numbers to positive and keep delta value nolenkw Excel Discussion (Misc queries) 3 August 11th 06 05:15 PM
Convert a column of numbers from positive to negative in Excel JRoseen Excel Discussion (Misc queries) 4 July 7th 06 07:18 PM
convert negative numbers to positive numbers and vice versa bill gras Excel Worksheet Functions 4 December 7th 05 02:39 AM


All times are GMT +1. The time now is 01:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"