check data

[jagguy]

Hi,

I want to create a macro that can check each the data of each cell to make sure it is a number before summing them .
eg sum cells a1-a20 and check if each cell has a number in it.

I know how to create a if statement but not to check on all cells with a macro.

 

[schielrn]

Welcome to the Board!

You could do something like this:

Sub test()
dim total as double
for i = 1 to 20
If isnumeric(cells(i,1).value) then
total = total + cells(i,1).value
end if
next i

msgbox("The total is " & total)
end sub

 

[mikerickson]

This function will signal if all the cells in the range contain numbers.

Function allAreNumbers(inRay As Range)
    Dim cellCount As Long
    cellCount = inRay.Cells.Count
    On Error Resume Next
    cellCount = cellCount - inRay.SpecialCells(xlCellTypeConstants, 1).Cells.Count
    cellCount = cellCount - inRay.SpecialCells(xlCellTypeFormulas, 1).Cells.Count
    On Error GoTo 0
    allAreNumbers = (cellCount = 0)
End Function

 

[Smitty]

Welcome to the Board!

Why a macro? SUM will ignore text and only operate on numbers.

I.E.

Book3
	A	B	C	D
1	1
2	2
3	3
4	a
5	c
6	b
7	4
8	5
9	6
10	e
11	f
12	g
13	21
Sheet1

But here's some code that will do the same thing in the manner you asked (but I don't understand why):<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> foo()
    <SPAN style="color:#00007F">Dim</SPAN> c<SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> i
        i = 0
            <SPAN style="color:#00007F">For</SPAN><SPAN style="color:#00007F">Each</SPAN> c<SPAN style="color:#00007F">In</SPAN> ActiveSheet.Range([A1], Cells(Rows.Count, "A").End(xlUp))
                <SPAN style="color:#00007F">If</SPAN> IsNumeric(c)<SPAN style="color:#00007F">Then</SPAN> i = i + c.Value
            <SPAN style="color:#00007F">Next</SPAN> c
        MsgBox i<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Smitty

 

[jagguy]

Hi,

Thanks for the replies.

What about also using data validation tool? Is there a problem with this?

So I can say only enter in numbers or just strings with no numbers and display an error message if i do?.

 

[mikerickson]

That would work. The ISNUMBER function would be used.

 

[jindon]

Date?

Sub test()
Dim r As Range, myTotal As Double
For Each r In Selection
     If (IsNumeric(r.Value)) * (Not IsDate(r.Value)) Then myTotal = myTotal + r.Value
Next
MsgBox myTotal
End Sub