I exported my Crosstab query from Access into Excel since the date ranges
vary and the end column has to be totalled up. I try using the NumberFormat:
See code below. The total cell does just rounds the number even though it
shows 2 decimal places.

'Add up the totals horizontally to get a sum amount.
For i = intDataStartRow To intRows
intValue = 0
For j = intDataStartCol To intCols
Cells(i, j).NumberFormat = "0.00"
intValue = Cells(i, j).Value + intValue
Next j 'Next column
Cells(i, j).NumberFormat = "0.00"
Cells(i, j).Value = intValue 'insert the sum now that we are done
with the column.
Cells(i, j).Font.Bold = True 'bold it
Next i 'Next row

Thanks in advance!!
From Access Guy

This might be a silly question but why use the crosstab query whey you can
just attach a pivot table directly to your source data (not limited to the
65,536 rows in Excel) in Access and then you get all of the functionality of
the pivot table which is a lot better than the cross tab. That way you can
avoid the code...
--
HTH...

Jim Thomlinson


"Craig" wrote:

I exported my Crosstab query from Access into Excel since the date ranges
vary and the end column has to be totalled up. I try using the NumberFormat:
See code below. The total cell does just rounds the number even though it
shows 2 decimal places.

'Add up the totals horizontally to get a sum amount.
For i = intDataStartRow To intRows
intValue = 0
For j = intDataStartCol To intCols
Cells(i, j).NumberFormat = "0.00"
intValue = Cells(i, j).Value + intValue
Next j 'Next column
Cells(i, j).NumberFormat = "0.00"
Cells(i, j).Value = intValue 'insert the sum now that we are done
with the column.
Cells(i, j).Font.Bold = True 'bold it
Next i 'Next row

Thanks in advance!!
From Access Guy