UDF causes #VALUE! error

[DocBrown]

I have a User Defined Function that is calculating a sum based on the
contents of other cells. It seems to work execpt that when I change a cell
the formula depends on the correct value is display momentarily then the
#VALUE! is displayed. If I hit F9, the correct value is displayed.

I suspect some problem with the calculation order of the dependencies, but
would appreciate help in figuring out what is causing the problem.

I have a second UDF that is checking the state of the Autofilter fields that
also seems to do this.

The both functions are below, incase that is needed.

Thanks,
john

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

Function MyFilters(MyRange As Range) As String
Application.Volatile
Dim i As Integer
'Debug.Print "MyFilters:" & Selection.Address & " " & MyRange.Address
MyFilters = False
With MyRange.Parent.AutoFilter
If Intersect(MyRange, .Range) Is Nothing Then Exit Function
For i = 1 To .Range.Columns.Count
With .Filters(i)
If .On Then
MyFilters = True
End If
End With
Next
End With

End Function

 

[Bernie Deitrick]

Doc,

I seem to recall some problem with using Find within a UDF... But your UDF seems like it could be
replaced with worksheet functions easily enough: either SUMIF or SUMPRODUCT, so describe what the
functions actually does, and perhaps we can suggest a native function.

HTH,
Bernie
MS Excel MVP

 

[Ron Rosenfeld]

I have a User Defined Function that is calculating a sum based on the
contents of other cells. It seems to work execpt that when I change a cell
the formula depends on the correct value is display momentarily then the
#VALUE! is displayed. If I hit F9, the correct value is displayed.

I suspect some problem with the calculation order of the dependencies, but
would appreciate help in figuring out what is causing the problem.

When I run into this kind of problem, I usually wind up setting multiple break
points and/or stepping through the routine to see where it fails.
--ron

 

[DocBrown]

Bernie,

I would LOVE to be able to do this with a native function. The algorithm has
multiple parts. See my other post under Programming called 'Sumproduct
forumla for complex sum' (sic). (I don't know how to create a link to the
posting.)

Thanks,
john