macro to calculate cell vlaues not working on decimals

[jowatkins]

Hi guys,

i've got the following bit of code to cycle through the sheets of
workbook and calculate the total vlaues for each column and then plac
the value in the corresponding cells on the master sheet. The code i
simple but I can't see why it won't yake values such as 0.5 int
account. Even changing the format to increase the numbers after th
decimal place has no effect.


For Each sheet In sheetNames()
If sheet <> "" Then
'cellTotal = 0
Set currSheet = baseBook.Sheets(sheet)
currCellValue = currSheet.Range(cellAddress).Value
cellTotal = cellTotal + currCellValue
'place cellTotal into cell in month sheet
monthSheet.Range(cellAddress).Value = cellTotal
If cellTotal > 0 Then
monthSheet.Range(cellAddress).Interior.Color = RGB(200
200, 200)
End If
End If
Next

any ideas?

Cheers, J

 

[Bob Phillips]

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Jo,

Have you declared the cellTotal variable? If so, as what?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[jowatkins]

cellTotal is declared as integer. I've tried declaring it as long, bu
this didn't work either

 

[Bob Phillips]

Thought so. Declare it as Double.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[jowatkins]

Hi Bob,

Thanks for replying, but that didn't work either.

Dim cellTotal As Double
Dim currCellValue As Double

i've tried adding this line to catch a decimal value, but it just skip
right passed it.

If currCellValue = 0.5 Then MsgBox currSheet & cellAddres

 

[Bob Phillips]

Jo,

It works fine for me. I knocked up this simple code , with 4 values of
1,2,3,4.5

Sub test()
Dim ttl As Long 'Double
Dim i As Long

For i = 1 To 4
ttl = ttl + Cells(i, "B").Value
Next i

MsgBox ttl

End Sub

With ttl declared as Long I get 10, as Double I get 10.5.

Are you sure the cell has exactly 0.5, and not say 7.5 in it. If the latter,
the test is bypassed.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)