Excel VBA

css navigation by Css3Menu.com

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.


© 2003-2016

Updated:  11/16/2016 19:50
This page added:  03 September 2003