Numbers have too many decimals

A spreadsheet created by exporting from QuickBooks as a .IIF file is opened in Excel 2003.

A macro multiplies a cell value and returns 1.77999997138977 rather than 1.78. The 1.78 is required for importing back into Quickbooks.

Part of the code is:

Please Login or Register  to view this content.

the tempNum in the IF line is 1.78, but ActiveCell.Value is not. So, though 1.78 appears in the spreadsheet cell, clicking on the cell displays 1.77999997138977 in the Formula Bar.

I am aware of the binomial floating point thingy, but can anyone solve this or find a workaround?

Welcome to Exceltip forum

Please take a couple of minutes and read all the Forum Rules


Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

I can't see what the problem is your code takes the activecell's value & multiplies it by a multiplier. Then formats to two decimal places, finally, finally removing the formula which results in a two decimal number. The code could be nater

Please Login or Register  to view this content.

I created a dummy sheet placed a decimal value in a cell, ran the code & the result as a two decimal number, even stepping through the code Post your workbook

What is your code actually supposed to do? You don't need to select cells & you seem to just loop to random row 6000.

When I follow your instructions it doesn't change the cell's value at all

I think this is doing what you want, without all the selecting and so will be much faster

Please Login or Register  to view this content.

Post back & let me know if it works OK

Re point 3
Dim Left(tempNum, 5) As Single
Dim is the 1st part of declaring a variable
Left(tempNum, 5) is to get the 1st 5 characters from the tempNum variable - It is not a variable name which is what you probaly require.
You would also need to do something with the extract 5 characters
As Single is the variable type - You may need a larger variable type like Double


example

Please Login or Register  to view this content.

In Roy's code I do not see anywhere that tempNum is populated which will give an error when trying to Left(tempNum, 5)

I can't remember exactly, but it was possibly in the code that I edited. I think the relevant variable I declared as a double. It might just be that I copied a pasted without editing correctly.