Heh Heh. I was too wimpy to ask, then eventually pieced it together. Thanks
for your help. Also, I enjoy reading what is, to my eye, elegant code. Wish I
had more of your skill set.
"Bob Phillips" wrote:
US+DF? What's that? I meant a UDF.
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"Bob Phillips" wrote in message
...
You could always build a US+DF that will evaluate it, and use that within
SP
'---------------------------------------------------------------------
Function fnEval(rng As Range, condition As Range) As Variant
'---------------------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim aryValues As Variant
If rng.Areas.Count 1 Then
fnEval = CVErr(xlErrValue)
Exit Function
End If
If rng.Cells.Count = 1 Then
aryValues = rng
Else
aryValues = rng.Value
i = 0
For Each row In rng.Rows
i = i + 1: j = 0
For Each cell In row.Cells
j = j + 1
aryValues(i, j) = rng.Parent.Evaluate(cell & condition)
Next cell
Next row
End If
fnEval = aryValues
End Function
=SUMPRODUCT(--(fnEval(B1:B10,D5))
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"John V" wrote in message
...
Thanks EdMac. I had spent a lot of time on that webpage, and you're
right,
it
is excellent. However, I could find no example of where the comparison
operator was not "hard-wired".
I would like to use cell references so the user could specify the
desired
operator without altering the function itself. I suppose I could have a
complicated IF statement that looked for occurences of <,,<,=, etc.
and
then performed the appropriate SUMPRODUCT function. But I was hoping a
more
elegant solution had been found.
Hope this is clearer, and thanks. JV
"EdMac" wrote:
Have a look at
www.xldynamic.com/source/xld.SUMPRODUCT.html
This will tell you just about all there is to know about this function
Ed
--
EdMac
------------------------------------------------------------------------
EdMac's Profile:
http://www.excelforum.com/member.php...o&userid=30736
View this thread:
http://www.excelforum.com/showthread...hreadid=532224