Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Function to inverse negative values! (Excel 2000 >)

    The code in this UDF will convert inverse negatives ( 34-) to the correct format (-34).:

    Function MirrorNegatives(Negative_Value As String) As Double
    Dim NewVal As String
    If Right(Trim(Negative_Value), 1) = "-" Then
    NewVal = Right(Trim(Negative_Value), 1) & Negative_Value
    Else
    NewVal = Negative_Value
    End If
    MirrorNegatives = Application.WorksheetFunction.Substitute( _
    NewVal, "-", "", 2) + 0
    End Function

    Copy it to your Workbook or to the personal macro workbook to increase its scope. You can also save it as an Add-In if desired!
    Regards,
    Rudi

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Function to inverse negative values! (Excel 2000 >)

    or:<pre>Function MirrorNegatives(Negative_Value As String) As Double
    MirrorNegatives = CDbl(Negative_Value)
    End Function
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Function to inverse negative values! (Excel 2000 >)

    Sheez...thats a lot easier. I did not know that CDbl converts to positive. Is this a natural behaviour?
    Regards,
    Rudi

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Function to inverse negative values! (Excel 2000 >)

    It doesn't convert to a positive - it coerces the input value to a double. There's obviously something in there that recognizes trailing negatives. Whether you could call that natural depends on your definition! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Function to inverse negative values! (Excel 2000 >)

    Thanx Rory. Its a great shortcut
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function to inverse negative values! (Excel 20

    Sounds like you've been having to cope with SAP for quite a long time! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Gre

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •