Decimal places - VBA

[ExcelChampion]

Something that I've worked around for years, and finally want to fix is, when a formula returns a number that extends out to more than two decimal places, the following code ends up rounding the number to the nearest hundredth, where rng is an array:

Range(rng).value = Range(rng).value

Over the years I've experimented with creating a Variant data type, as in:

Dim myDec as Variant
myDec = Range(rng).value
Range(rng).value=myDec

But to no avail...

The only thing that I know that works is using the Copy/PasteSpecial method, but I don't like commandeering the user's clipboard to do stuff for obvious reasons.

What is the best process for returning the value from a formula and retaining its precision? Anyone try the sub-data type, Decimal?

Thanks.

 

[mole999]

have a read here http://www.mrexcel.com/forum/excel-...decimal-places-visual-basic-applications.html

 

[ExcelChampion]

Thanks, but its still a no-go.

I've tried that before (and I just tried it again to be sure), and though I'm not positive, I think it throws up a 'Type Mismatch' flag because the cell(s) actually contain formulas. I'm trying to convert the formulas to values.

So, using actual code:

Dim myDec as Double
myDec = .Cells(1, lCol + 21).Offset(26, 0).Resize(lRow - 1, 4).Value
.Cells(1, lCol + 21).Offset(26, 0).Resize(lRow - 1, 4).Value = myDec

It errors (Type mismatch) on: myDec = .Cells(1, lCol + 21).Offset(26, 0).Resize(lRow - 1, 4).Value

 

[RoryA]

Try using:

Range(rng).value2 = Range(rng).value2

 

[Andrew Poulsom]

You can't pass the values of multiple cells to a variable declared as Double. It has to be declared as Variant to contain an array.

 

[ExcelChampion]

You can't pass the values of multiple cells to a variable declared as Double. It has to be declared as Variant to contain an array.

I was just thinking that too.

 

[ExcelChampion]

Try using:

Range(rng).value2 = Range(rng).value2

You da' man. I should have known that Value2 was it. Woulda', coulda', shoulda'...

Thank you, Rory.