You haven't told VBA that sumValues depends on Sheet1!A1:A10000, so it
doesn't know to recalculate sumValues when a value in that range changes.

Either add

Application.Volatile

at the beginning of the function, or include the range as an argument to
the function.

However, I'd think you could do much faster and more efficient with a
built-in function:

=SUMIF(Sheet1!$C$1:$C$10000, strName, IF(strType="D",
Sheet1!$G$1:$G$10000, Sheet1!$H$1:$H$10000))


In article <op.ttgp7tizupgxg0@descstar, DesCF wrote:

Here is the function I am using. It is used in sheet 2 and references
cells in sheet 1. It is used twice in two adjacent cells and the results
are then summed in a third adjacent cell. In a forth adjacent cell the
figure in the third adjacent cell is added to another figure taken from
the immediately preceding row in the forth adjacent cell.


Public Function sumValues(strName As String, strType As String) As Currency

Dim intI As Integer
Dim intJ As Integer
Dim curC As Currency

Select Case strType
Case "D"
intJ = 7
Case "C"
intJ = 8
End Select

With Sheet1
For intI = 1 To 10000
If .Cells(intI, 3) = strName Then
curC = curC + .Cells(intI, intJ)
End If
Next
End With

sumValues = curC

End Function