value put in cel adds unwanted decimals

Negentropy

New Member
Joined
Jul 22, 2010
Messages
2
Hi,<o:p></o:p>
I’m dealing with the following puzzle, and hope there’s someone here that can solve the mystery;<o:p></o:p>

(Office 2010) When reading a value from an Access database being the number 0,2 (I’m in a country that uses , to note the decimals) and putting that value into an Excel cell, magically there are numbers added at the end of the decimals. Using Round in VBA doesn’t solve the issue. This is what I’m doing:<o:p></o:p>

‘reading the value from the database, vtSql is my SQL code<o:p></o:p>
Set rs = db.OpenRecordset(vtSql)<o:p></o:p>

‘Put the value in Sheet1 range A1, rs.Value = 0,2<o:p></o:p>
Sheets("Sheet1").Range("A1").Value = rs.Value<o:p></o:p>

Now if I look in A1 the number there = 0,200000002980232 instead of the expected 0,2000etc<o:p></o:p>

What I’ve tried and doesn’t work:<o:p></o:p>
1. Sheets("Sheet1").Range("A1").Value =Round( rs.Value, 3)<o:p></o:p>
2. Set rs.value to a variable and round that variable <o:p></o:p>

What I’ve tried and does work but makes me use extra code, is put the value into range A1 (which has the weird numbers added to the decimals) and then read that value again From range A1, round it, and then put it back:<o:p></o:p>
Range("A1").Value = Round(Range("A1").Value, 3)<o:p></o:p>

As you can tell I have a solution to the problem, however I’m not too keen on using extra code in programming for something that should work to begin with imho. Also I’m just very curious at what is causing this whole thing. I’m aware the value that’s wrong is very very small however it’s used in conditional formatting and thus messes up de formats of cells, of course there’s also a way around that, which again requires extra code and just makes the whole thing messy.<o:p></o:p>

I’m curious if anyone can solve this puzzle. Cheers!<o:p></o:p>

Chris<o:p></o:p>
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Does this work?

Sheets("Sheet1").Range("A1").Value = WorksheetFunction.Round(rs.Value, 3)

I expect floating point arithmetic is the cause of your problem.
 

Negentropy

New Member
Joined
Jul 22, 2010
Messages
2
Does this work?

Sheets("Sheet1").Range("A1").Value = WorksheetFunction.Round(rs.Value, 3)

I expect floating point arithmetic is the cause of your problem.

Works perfectly sir, thank you :LOL:

I expect the floathing point stuff has something to do with it, however I still find it strange as the value in VBA appears to be also simply known as 0,2 when checking;
If rs.value > 0.2 Then
MsgBox "bigger"
Else
MsgBox "not bigger"
End If
'result = not bigger, normally meaning it is 0,2 and not 0,2 and a bit
msgbox rs.Value 'this also simply shows 0,2

The whole extra numbers don't seem to come in to play until the value is actually added to the Excel sheet.

Thanks again, you saved me some messy coding :)

Chris
 

Watch MrExcel Video

Forum statistics

Threads
1,122,658
Messages
5,597,403
Members
414,142
Latest member
Banyangt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top