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
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