You need to add volatile to your function

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

On Fri, 01 Jun 2007 13:47:00 +0100, Mike H
<MikeH@discussions.microsoft.com> wrote:

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