variable type changing number of decimal places

Here's what I'm doing: I'm using a macro to assign a cell value to a variable then set another cell value to the variable instead of copy/paste (because even pasting values only was affecting other formulas in the file for some reason)

The problem: in using the macro, the number being 'copied' is acquiring a few extra decimal places IE 38334.61 is the original number and 38334.609375 is what I end up with. The numbers come from a CSV with only 2 decimal places and I checked by adding decimal places in the format so it's not a formatting/visible digits issue. Since the values are hour meter readings, the extra decimal places end up with very small values outside 0-24 hrs which messes with sorting and usage %. The values are so small all of them together in a month add up to a fraction of a cent but it's one of those things that bugs my OCD by not being right.

My VBA book explains the min/max capabilities, content type, memory bits but not fiddly details like this.

So my question is this: right now I'm declaring the clipboard-substitue variable as an single, is there a different one that would work better without adding anything? Criteria are: numeric, 6 digits before the decimal, 2 after, all positive values. (Ie 123456.12)

EDIT: fixed my senior moment.

If your variable is an integer, it won't have any decimal places so I'm a little confused. Could you post your code?

Originally Posted by romperstomper

If your variable is an integer, it won't have any decimal places so I'm a little confused. Could you post your code?

Senior moment. It's a "single" not integer (and no I didn't change it).

Basically it does two things: increments columns if I get behind (the loop and db variable) and it moves hour meter readings from the top block of data to the lower block. the difference is that if the contents of a room change, a new group gets billed for their time in there thus a new line with their billing info (hence the empty blocks of data to the left, above, below in the example sheet)

BTW I didn't write this, I just changed it to use the macro variable as a clipboard instead of copy/paste/clearing cells then having to repair the formulas elsewhere. I also removed more commented out obsolete code used for development. Then again, I don't know a better way either.

Please Login or Register  to view this content.

hm decimal places.xlsx (problem items highlighted for expediency)

^That is why I asked here I figured it was something along those lines. Thanks!

Now let me get some water for my brain. It makes sense but jeez, it's not even 9am...

The results don't make since to me (nothing in the code is changing the number of decimals), but I would try the double data type for hm (instead of single) as that is what excel uses for worksheet calculations, this will fix your issue.

Edit: Was posting while joeu2004 gave a great answer - note: I was right too but didn't know why!

Regards,

Well, you can have a star too Tom, since you still knew the answer even if you couldn't show your work lol