Automate

Welcome to the Automate community!  Please feel free to start a discussion in the discussion tab or join in a conversation.

Here are some useful links where you can find more information:

Product Announcements  Ideas Portal

Discussions

Members

Resources

Events

 View Only

  • 1.  Convert 30.000- downloaded value to a number

    Posted 02-02-2019 16:52

    Hello,

     

    I'm writing a script (version 11.4.1) in VK12 where I download a percent value, then need to upload it minus 3% .  I'm having trouble because it gets downloaded in the SAP number format with the minus sign after the number i.e. 30.000- so my formula to subtract 3% from it isn't working because the downloaded value isn't recognized as a number.

     

    Is there a way to use the transform functionality to handle this?  Or some formula or functionality in excel to achieve the desired results so I can re-upload the value as -27.00??

     

    Any suggestions would be greatly appreciated.

     

    Rgds,

    Rita

     

     

    Rita Schoeppe
    Business Process Analyst, Quote to Cash

    t: 616.393.3124 | c: 616.340.1988 | e: rita.schoeppe@haworth.com
    One Haworth Center, Holland, MI 49423-9576 USA | 616.393.3000

    haworth.com

    Haworth Logo

     

    We believe inspiring spaces can enrich lives and businesses around the world.

     

    Confidentiality Notice: This email message and its attachments may contain confidential information. If you received this message in error, please delete it as soon as possible and alert the sender.

     



  • 2.  RE: Convert 30.000- downloaded value to a number

    Posted 02-04-2019 03:55
    Hi Rita,

    I did this in VBA.

    Sub ConvertMinusAtEnd(rngToChange As Range)
        Dim rngCell As Range
       
        For Each rngCell In rngToChange
            If Right(rngCell.Value, 1) = "-" Then
                rngCell.Value = (Left(rngCell.Value, Len(rngCell.Value) - 1) * -1) + 3
            Else
                rngCell.Value = (CLng(rngCell.Value) * 1) - 3
            End If
        Next rngCell
    End Sub​

    You can call this like so in the VBA Immediate window:
         call ConvertMinusAtEnd(Range("C6:C7"))

    The code has + 3 since if you want to go from -30% to -27% you would need to add 3 not subtract 3

    If you want additional code to determine the range, let me know the worksheet name and the column the percentages are in.

    I hope this helps.



    ------------------------------
    Mark Tiller | Delivery Enhancement Developer
    Transport for London (TfL) | 0203 054 4196
    ------------------------------



  • 3.  RE: Convert 30.000- downloaded value to a number

    Posted 02-04-2019 04:12
    Hi Rita,

    If a simple formula would be easier.  this assumes you percentage is in column C:

         =IF(RIGHT(C2,1)="-",LEFT(C2,LEN(C2)-1)*-1+3,C2-3)​

    ------------------------------
    Mark Tiller | Delivery Enhancement Developer
    Transport for London (TfL) | 0203 054 4196
    ------------------------------

    Original Message