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

All,

I'm 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

iAY = ActiveSheet.Range("ad536")
iBY = ActiveSheet.Range("ad537")
dblOpEx = ActiveSheet.Range("ad538")
dblPctInc = ActiveSheet.Range("ad539")
dblCurrent = ActiveSheet.Range("ad540")
dblAddOnExp = ActiveSheet.Range("ad542")

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

"AJ Master" > wrote:
> I'm 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.
[....]
> If iBY > Year(ActiveCell.Offset(j, -6)) Then
> ActiveCell.Offset(j, 1).Value = dblCurrent
[....]
> 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?

I suspect that the cells are formatted with 2 decimal places __and__ the
"Precision as displayed" (PAD) calculation option is set.

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

On 3/08/2012 9:23 AM, AJ Master wrote:
> All,
>
> I'm 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
>
> iAY = ActiveSheet.Range("ad536")
> iBY = ActiveSheet.Range("ad537")
> dblOpEx = ActiveSheet.Range("ad538")
> dblPctInc = ActiveSheet.Range("ad539")
> dblCurrent = ActiveSheet.Range("ad540")
> dblAddOnExp = ActiveSheet.Range("ad542")
>
> 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
>

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