+ Reply to Thread
Results 1 to 4 of 4

CSng adds erroneous digits to the value

  1. #1

    CSng adds erroneous digits to the value

    I'm stumped with what's happening here. I have a value in a CSV that I
    want to round. I wanted to use CSng to force the single data type so
    that an error would not occur from trying to round a text string. My
    code is simple:

    Cells(i, 8).Value = Round(CSng(Cells(i, 10).Value, 4))

    where i is an integer used in a loop to run down the cells in columns 8
    and 10.

    For some reason when I set the value in Cell(i,8), Excel is adding some
    apparently random erroneous digits to the end of the value. So for
    example, a value appearing as 12.1294 in Cell(i,10) comes out as
    12.12946789 in Cell(i,8). It appears to be something that happens when
    I insert the value into a cell too. If I just display
    Round(CSng(Cells(i, 10).Value, 4))
    in a Msgbox everything looks fine. Anybody know what might be causing
    this? I'm running MS VB 6.0 with MS Excel 2000.


  2. #2
    NickHK
    Guest

    Re: CSng adds erroneous digits to the value

    Ben,
    I'm surprised you could get it to compile. Look at the bracket positions.
    Cells(i, 8).Value =Round(CSng(Cells(i, 10).Value), 4)

    NickHK

    <[email protected]> wrote in message
    news:[email protected]...
    > I'm stumped with what's happening here. I have a value in a CSV that I
    > want to round. I wanted to use CSng to force the single data type so
    > that an error would not occur from trying to round a text string. My
    > code is simple:
    >
    > Cells(i, 8).Value = Round(CSng(Cells(i, 10).Value, 4))
    >
    > where i is an integer used in a loop to run down the cells in columns 8
    > and 10.
    >
    > For some reason when I set the value in Cell(i,8), Excel is adding some
    > apparently random erroneous digits to the end of the value. So for
    > example, a value appearing as 12.1294 in Cell(i,10) comes out as
    > 12.12946789 in Cell(i,8). It appears to be something that happens when
    > I insert the value into a cell too. If I just display
    > Round(CSng(Cells(i, 10).Value, 4))
    > in a Msgbox everything looks fine. Anybody know what might be causing
    > this? I'm running MS VB 6.0 with MS Excel 2000.
    >




  3. #3
    Tom Ogilvy
    Guest

    RE: CSng adds erroneous digits to the value

    Assuming your formula had a typo, do you mean like this:

    activeCell.Value = round(cSng(12.1294),4)
    ? activecell.Value
    12.1294002532959

    The problem is that values in cells are stored as double. When you put a
    single in a double, you can pick up garbage on the end. the solution is to
    use cdbl rather than csng

    round(cdbl(12.1294),4))

    --
    regards,
    Tom Ogilvy




    "[email protected]" wrote:

    > I'm stumped with what's happening here. I have a value in a CSV that I
    > want to round. I wanted to use CSng to force the single data type so
    > that an error would not occur from trying to round a text string. My
    > code is simple:
    >
    > Cells(i, 8).Value = Round(CSng(Cells(i, 10).Value, 4))
    >
    > where i is an integer used in a loop to run down the cells in columns 8
    > and 10.
    >
    > For some reason when I set the value in Cell(i,8), Excel is adding some
    > apparently random erroneous digits to the end of the value. So for
    > example, a value appearing as 12.1294 in Cell(i,10) comes out as
    > 12.12946789 in Cell(i,8). It appears to be something that happens when
    > I insert the value into a cell too. If I just display
    > Round(CSng(Cells(i, 10).Value, 4))
    > in a Msgbox everything looks fine. Anybody know what might be causing
    > this? I'm running MS VB 6.0 with MS Excel 2000.
    >
    >


  4. #4

    Re: CSng adds erroneous digits to the value

    Yes, I did have a typo, and your solution worked. Thanks!
    Tom Ogilvy wrote:
    > Assuming your formula had a typo, do you mean like this:
    >
    > activeCell.Value = round(cSng(12.1294),4)
    > ? activecell.Value
    > 12.1294002532959
    >
    > The problem is that values in cells are stored as double. When you put a
    > single in a double, you can pick up garbage on the end. the solution is to
    > use cdbl rather than csng
    >
    > round(cdbl(12.1294),4))
    >
    > --
    > regards,
    > Tom Ogilvy
    >
    >
    >
    >
    > "[email protected]" wrote:
    >
    > > I'm stumped with what's happening here. I have a value in a CSV that I
    > > want to round. I wanted to use CSng to force the single data type so
    > > that an error would not occur from trying to round a text string. My
    > > code is simple:
    > >
    > > Cells(i, 8).Value = Round(CSng(Cells(i, 10).Value, 4))
    > >
    > > where i is an integer used in a loop to run down the cells in columns 8
    > > and 10.
    > >
    > > For some reason when I set the value in Cell(i,8), Excel is adding some
    > > apparently random erroneous digits to the end of the value. So for
    > > example, a value appearing as 12.1294 in Cell(i,10) comes out as
    > > 12.12946789 in Cell(i,8). It appears to be something that happens when
    > > I insert the value into a cell too. If I just display
    > > Round(CSng(Cells(i, 10).Value, 4))
    > > in a Msgbox everything looks fine. Anybody know what might be causing
    > > this? I'm running MS VB 6.0 with MS Excel 2000.
    > >
    > >



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1