Data Type Calculation Errors

thorburnc

New Member
Joined
Apr 26, 2007
Messages
25
I have a workbook that calculates a sum of numbers for each week requested (a reporting tool) from 7-8 worksheets within this workbook.

My problem is that sometimes a cell does not contain a number so it is blank and when the formula looks at it to add the value to the sum, it is reading it as a different data type and can't add the "0" value to the ongoing sum calculation.

I hope that makes sense...we keep getting a debug error because it can't sum up all the numbers.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
what is your formula ?

you could possibly create an array or just use a handler:

=IF(ISNUMBER(cell1),cell1,0)+IF(ISNUMBER(cell2),cell2,0)
 
Upvote 0
Well, it happens in two spots.

The first formula that I get this error is:

If c.Interior.ColorIndex = xlColorIndexNone Then dbl = dbl + c.Value

Then when I do the reporting for each of the sheets, the formula is

s = s + wksht.Cells(c + B, T).Value
 
Upvote 0
Same theory applies in VBA, you should test the variable before trying to add it, ie

Rich (BB code):
If c.Interior.ColorIndex = xlColorIndexNone And IsNumeric(c.Value) Then dbl = dbl + c.Value

Make sense ?
 
Upvote 0
Christine

An empty cell should be no problem when adding.

Care to post the rest of your code?

Perhaps you should be looking at some of the conversion functions like Val, CDbl, CInt etc.
 
Upvote 0
Function sumWhite(rng As Range)
Dim c As Range
Dim dbl As Double
For Each c In rng
If c.Interior.ColorIndex = xlColorIndexNone Then dbl = Sum(dbl + c.Value)
Next
sumWhite = dbl
End Function

That is my code...

I will try adding in the ISNumberic(c.value) and hopefully that works! Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top