VBA to Ignore #NUM! Errors in SUM()

[skibum0627]

How do I program in VBA to sum a row or column ignoring the #NUM! errors in some cells?


(The row I am trying to sum contains #NUM! errors in many different places)


By the way, the "#NUM!" errors do not contain formulas. It is "fixed" such that when you click on that cell, it only reads "#NUM!" in the F(x) box. Perhaps is there a way to make these errors blank so that the SUM() ignores these cells?

 

[chrish47]

Hi,

I assume this sheet is the result of copying and pasting the values from another sheet (only way I know of to get #NUM errors without directly entering them).

If that is the case, I would modify the sheet that the data comes from by adding error checking to the formula. This is not exact, but without any reference, I will use my vlookup formula:

=IF(isna(vlookup(x,y,z)),"",vlookup(x,y,z))

This looks up the value if it exists, or leaves a blank if it doesn't (instead of the standard #N/A error message returned by an invalid vlookup). Using the iserr() function and an IF statement in your original formula should work.

If you only have the sheet with the #NUM! cells, a simple find/replace (find #NUM! replace with "") on that column should take care of the problem.

Thanks,
Chris

 

[Zack Barresse]

Hi, welcome to the board!

If you had your data looking something like this ...

Book1
	A	B	C	D
1	1
2	2
3	3
4	4
5	5	15
6	#NUM!
7	#NUM!
8	#NUM!
9	#NUM!
10	#NUM!
11	#NUM!
12	6
13	7
14	8
15	9
16	10	40
17		FormulaCheck:	VBA
18		55	55
Sheet1

Then you could run a Standard procedure like this ...<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN><SPAN style="color:#00007F">Explicit</SPAN><SPAN style="color:#00007F">Sub</SPAN> AddMeUp()
    <SPAN style="color:#00007F">Dim</SPAN> cel<SPAN style="color:#00007F">As</SPAN> Range, rng<SPAN style="color:#00007F">As</SPAN> Range, dest<SPAN style="color:#00007F">As</SPAN> Range, tmpCel<SPAN style="color:#00007F">As</SPAN> Range, wf
    <SPAN style="color:#00007F">Set</SPAN> wf = Application.WorksheetFunction
    <SPAN style="color:#00007F">Set</SPAN> dest = Range("C18")
    <SPAN style="color:#00007F">Set</SPAN> rng = Range("A1", Range("A65536").End(xlUp))
    <SPAN style="color:#00007F">For</SPAN><SPAN style="color:#00007F">Each</SPAN> cel<SPAN style="color:#00007F">In</SPAN> rng
        <SPAN style="color:#00007F">If</SPAN> IsNumeric(cel.Value)<SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">If</SPAN> tmpCel<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Then</SPAN>
                <SPAN style="color:#00007F">Set</SPAN> tmpCel = cel
            <SPAN style="color:#00007F">Else</SPAN>
                <SPAN style="color:#00007F">Set</SPAN> tmpCel = Union(cel, tmpCel)
            <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> cel
    dest.Value = wf.Sum(tmpCel)<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>



HTH

 

[Tazguy37]

You could go through the range that's to be used (have the user select it before or after the routine starts) cell by cell, and if there's an error, skip that cell (or add 0).

Sub test()
Dim cell As Range
Dim Sum As Long
Sum = 0
For Each cell In Selection
    If Not (IsError(cell.Value)) Then Sum = Sum + cell.Value
Next cell
MsgBox (Sum)
End Sub

If your range is really long, you may want to look at a faster method. Hope that helps!

 

[skibum0627]

Thanks guys, you all are great!