I think I'm between a rock and a hard place. On one hand a UDF allows me to
calculate the sum I want. But on the other, a UDF on the WS causes another
macro called from Worksheet_change to fail with Error 1004.

See my other thread called 'UDF interaction with other macro causes Error
1004'

There's another feature that I want to add that also requires a UDF. So now
what?

Any ideas?

"p45cal" wrote:


DocBrown;494694 Wrote:
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: 'The Code Cage Forums - View Profile: p45cal'
(http://www.thecodecage.com/forumz/member.php?userid=558)
View this thread: 'Sumproduct forumla for complex sum. - The Code
Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=134340)



DocBrown,
I tried your function on the original data but I think it gives wrong
results; I stepped through it and (if I've got the ranges correct)
couldn't find
a) where the code added the $75
b) where the code added $50 for the 110.
Below, I've tweaked your function and tacked '2' onto the end of its
name:
Function SubTotalMatch2(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
Dim srcValue
Total = 0

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

For cellIndex = 1 To rngSource.Count
srcStr = rngSource.Cells(cellIndex).Value
srcValue = rngSubTotal.Cells(cellIndex).Value
If srcStr = "" Then
Total = Total + srcValue
Else
Set cCell = rngMatch.Find(srcStr, LookIn:=xlValues)
If cCell Is Nothing Then
Total = Total + srcValue
Else
If cCell.Offset(0, 3).Value = "" Then
Total = Total + srcValue
End If
End If
End If
Next cellIndex

SubTotalMatch2 = Total ' + Application.Sum(rngMatch.Offset(, 3))
'un-comment for grand total
End Function
It gives the same results now as my macro mentioned
earlier.
I haven't looked too hard for a non-udf solution.


--
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