Sub testit()
Dim rngSource As Range, rngFilter As Range, rng As Range, dblMySum As
Double

Set rngSource = Range("A1:A1000")

For Each rng In rngSource
If IsNumeric(rng.Value) And rng.Value = 500 Then
If rngFilter Is Nothing Then
Set rngFilter = rng
Else
Set rngFilter = Union(rngFilter, rng)
End If
End If
Next

rngFilter.Select
For Each rng In rngFilter: dblMySum = dblMySum + rng.Value: Next
MsgBox dblMySum
End Sub


"Charley Kyd" wrote in message
...
I have a spreadsheet column that could include empty cells, zero values,
strings, and non-zero values. I would like to define a Range object that
references only the non-zero values. I'd prefer not to loop.

Ideally, the method also would allow me to define a Range based on other
criteria, like cells with values greater than 100, or whatever.

This is easy to do in an array formula in a spreadsheet, with something
like:
=Sum(If(Foo100,Foo,0))

I've been playing around with FormulaArray. But I don't see a clean way to
define a range object based on this property.

Does anyone have any ideas?

Thanks.

Charley