yea, the code is a bit obscure. *grin* I'm sure I can clean it up a bunch.

The test data columns correspond to the real data as follows:

A = E = rngSource
B = H = rngSubTotal
E = L = rngMatch
F = O = rngMatch.Offset( ,3)

I probably should pass in that 4th column, or a multi-column range. Yes,
they were intended to be single columns. As you see, the real data has other
columns between the columns I want to reference.

John

"p45cal" wrote:


DocBrown;493716 Wrote:
I've figured out a solution. I wrote a function that performs the sum
that I
want and I call that from the formula for the cell. It seems to work,
almost.
The function causes #VALUE! to be displayed. F9 clears it and displays
the
correct value.

I have a second function referenced in a different cell that also
displays
#VALUE!. I have no idea why that is.

I'd still like to see a solution that doesn't use a UDF.

John

Here's the function. Note the cell references are different because
these
are the columns on my live workbook. Comments on this function are
welcome.

Function SubTotalMatch(rngSource As Range, rngMatch As Range,
rngSubTotal As
Range) As Currency
Application.Volatile
Dim cCell As Range
Dim cellIndex As Integer
Dim srcStr As String
Dim Total As Currency

Total = 0

If rngSource.Count < rngMatch.Count Or rngSource.Count <
rngSubTotal.Count
Then
SubTotalMatch = 0
Exit Function
End If

For cellIndex = 1 To rngSource.Count
srcStr = rngSource.Cells(cellIndex).Value

Set cCell = rngMatch.Find(srcStr, LookIn:=xlValues)
If Not cCell Is Nothing Then
If cCell.Value < "" And cCell.Offset(0, 3).Value = "" Then
Total = Total + rngSubTotal.Cells(cellIndex).Value
End If
End If
Next cellIndex

SubTotalMatch = Total
End Function


DocBrown,
could you clarify the relationship between your original
"Tbl1 A & B, Tbl2 E & F"
and the ranges
ngSource, rngMatch, rngSubTotal
in the code above?
Also should they be single columns?
I'm a bit puzzled with the 3 in:
And cCell.Offset(0, 3).Value


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=134340