Macro to move "-"

[stapuff]

I am not sure of the author of this code - so I can't give proper credit. It is not doing exactly as I need so I need some help.

I have data downloaded where negative numbers are shown as 35,650- which is viewed by Excel as text. The code below moves the "-" from the right to the left thus allowing Excel to understand it as a negative which is what I need.

The problem is it get's trimmed. 35,650- shows as -35 not -35,650. What needs to change to show the whole #?

Thanks,

Kurt


Private Sub texttonegative()
Dim MemberCell As Range, DACELL
For Each MemberCell In ActiveSheet.UsedRange
If Right(MemberCell.Formula, 1) = "-" Then
MemberCell.Formula = Trim(MemberCell.Formula)
For DACELL = 1 To Len(MemberCell.Formula)
If Mid(MemberCell.Formula, DACELL, 1) = Chr(44) Then 'IF HAS COMMA
MemberCell.Formula = Left(MemberCell.Formula, DACELL - 1) _
& Right(MemberCell.Formula, Len(MemberCell.Formula) - 2)
End If
Next
MemberCell.Formula = _
Val("-" & Val(Left(MemberCell.Formula, _
Len(MemberCell.Formula) - 1))) 'Formula for moving sign
End If
Next
End Sub

 

[Mark W.]

Checked the advanced button, it only gave me options to do with the Decimal and Thousand seperator.

Okay, so it was added in 2002. For users of earlier versions, I'd recommend the use of ASAP Utilities (www.asap-utilities.com).

 

[Greg Truby]

...Either way works just as well tho

I don't know that I would go so far as to say "either way works just as well". Each approach has it's own merits:<ul>[*]Hard-coding negatives as red means that if you are then scanning the numbers and for whatever reason the font is red but the value is positive, you have a red flag (no pun intended) that something's amiss.[*]On the other hand, if the values might later be edited/corrected by hand, so there's a chance that the user might deliberately be changing negatives to positives then the conditional may be preferable.[/list]

 

[stapuff]

Thanks for all your posts.

My last place of employment has 2002. My current doesn't so I knew I couldn't use text to columns.

I have also(about a month ago) downloaded ASAP utilities. A great tool.

The idea behind the need is a AS400 report is download into a system drive. My macro get's it, filters it, then auto emails the selection with the click of 1 button through Lotus Notes. I had everything working but the native way the AS400 shows a negative is text by Excel's standards. The code I posted worked for me (to a point). I never had a number greater then 1000 so it appeared to be working as I did need it until I got the 1st neg>1000. The sheet has a conditional format to change when <0 so I had that also.


Norie came up with a solution on Ozgrid Forum. I post there also because Mr. Excel page was not allowing me on after the original post. This solution works as I needed it.

Again,

Thanks to all of you for your time, energy, and willingness to help me out.

Kurt

Private Sub texttonegative()
Dim MemberCell As Range
For Each MemberCell In ActiveSheet.UsedRange
If Right(MemberCell.Formula, 1) = "-" Then
MemberCell.Value = -Trim(Left(MemberCell, Len(MemberCell) - 1))
End If
Next
End Sub