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.

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

wrote in message
oups.com...
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.

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




" 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.

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




" 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.