Results 1 to 3 of 3
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Numbers Truncated (VBA/Excel/XP)

    I have this little piece of code I wrote to fix numbers that have been received in a text from from SAP with the minus sign on the right rather than left that Excel recognizes.

    <pre> For G = 42 To 229 Step 17
    tsCol = Mid(InputRow, G, 15) 'Get numb from text
    Trim (tsCol) 'Trims SPACES
    If Right(tsCol, 1) = "-" Then 'Eval whether minus
    tsCol = "-" & Left(tsCol, Len(tsCol) - 1) 'reArrange
    End If
    Cells(dIs, T) = tsCol 'Plops in place
    T = T + 4
    Next</pre>




    The data looks like:
    <pre>| 900 |2004 |C008 |892100| | | 1,389.37-| 21,941.90-| 1,339.34-|
    | 900 |2004 |C011 |231101| | | 0.00 | 0.00 | 0.00 |
    | 900 |2004 |C011 |231101| | | 12,111,435.00 | 0.00 | 0.00 |
    | 900 |2004 |C011 |240001| | | 0.00 | 0.00 | 2,579,451.00-|
    </pre>



    If the numbers are small, it comes in fine. But for the long numbers with multiple commas, the number is truncated to single digits.

    Should I strip out the commas before starting the conversion? Is there an easy way?
    Alan

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Numbers Truncated (VBA/Excel/XP)

    Does

    tsCol = CCur("-" & Left(tsCol, Len(tsCol) - 1)) 'reArrange & convert to currency

    fix the problem? (You will probably have to Dim tsCol as Variant.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Numbers Truncated (VBA/Excel/XP)

    It works. <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

    Instead of defining as Variant, found that defining as Double as better. Variant was putting bogus <pre>-------</pre>

    in the cell.

    Thanks a bunch!
    Alan

Posting Permissions

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