# Excel - Variable Defined as Double is entered in cell with only 2 decimal places

Asked By AJ Master on 02-Aug-12 07:23 PM
```All,

I am having trouble trying to debug why variables defined as double and
used in calculations appear with only 2 decimal places when placed in
the cell.  Here is some of the code.

.
.
.
Dim dblOpEx, dblCurrent, dblAddOnExp, dblPctInc As Double
Dim iBY, iAY, i, j As Double

For j = 0 To iRowCount + iEmptyCount - 1

If j < iEmptyCount Then
ActiveCell.Offset(j, 1).Value = 0

Else

If iBY > Year(ActiveCell.Offset(j, -6)) Then
ActiveCell.Offset(j, 1).Value = dblCurrent
Else
ActiveCell.Offset(j, 1).Value = (dblCurrent +
dblAddOnExp) * (1 + dblPctInc) ^ (Year(ActiveCell.Offset(j, -6)) -
iAY)
End If

End If

Next j

The cell value of dblcurrent is 9.025004, but when it enters it in
cells BI704 thru BI717 it rounds it to 9.03 and this causes
calculation errors.  Any idea why this is happening?

Any thoughts would be appreciated.---AJ```

joeu2004 replied to AJ Master on 02-Aug-12 08:02 PM
```[....]
[....]

I suspect that the cells are formatted with 2 decimal places __and__ the

As a work-around, if not a solution, try the following:

ActiveCell.Offset(j, 1).NumberFormat = "General"
ActiveCell.Offset(j, 1) = dblCurrent

Better still, disable PAD, and use explicit rounding wherever appropriate.
I usually deprecate the use of PAD for the very problem (among others) that
you encountered:  its effect is too pervasive.

For future note, when you have a problem that specific, it is prudent to
reduce the code to the minimum necessary to demonstrate the problem.  It
should have been sufficient to write:

Sub testit()
Dim dblVal As Double
dblVal = 9.025004
Cells(1, 1) = dblVal
End Sub```
Living the Dream replied to AJ Master on 03-Aug-12 07:54 AM
```Hi

You could try setting the format at the same time as setting the value.

eg

If iBY > Year(ActiveCell.Offset(j, -6)) Then
with ActiveCell
.Offset(j, 1).Value = dblCurrent
.Offset(j, 1).NumberFormat = "0.000000"
End with

Set the number of decimal places to whatever suits your need.

HTH
Mick```