Move the Minus Sign to Front
Information is coming out of our SAP system with the minus sign on the back end but we need it on the front.
Sub ConvertMinus()
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
After working with it a while, we found a few other anomalies. Numbers came over with the comma already part of the number. This code strips out the comma by taking the first part, ignores the comma, and puts the back end in place.
|