Hi,

I wrote an VB function:

Function CheckSession(S1 As String, S2 As String, S3 As String, S4 As
String, S5 As String, R1 As Integer, R2 As Integer, R3 As Integer, R4 As
Integer, R5 As Integer) As String


Dim Session As String
Dim C1 As Integer
Dim C2 As Integer
Dim C3 As Integer
Dim C4 As Integer
Dim C5 As Integer
'C1 = 5
'C2 = 6
'C3 = 7
'C4 = 8
'C5 = 9

C1 = Application.WorksheetFunction.SumIf(Range("AL5:AL3 807"), "1",
Range("AP5:AP3807"))
C2 = Application.WorksheetFunction.SumIf(Range("AL5:AL3 807"), "2",
Range("AP5:AP3807"))
C3 = Application.WorksheetFunction.SumIf(Range("AL5:AL3 807"), "3",
Range("AP5:AP3807"))
C4 = Application.WorksheetFunction.SumIf(Range("AL5:AL3 807"), "4",
Range("AP5:AP3807"))
C5 = Application.WorksheetFunction.SumIf(Range("AL5:AL3 807"), "5",
Range("AP5:AP3807"))


Session = ""

If ((S1 < "CLOSED") And (S1 < "N/A")) Then
If C1 < R1 Then
Session = S1
End If
End If
If ((S2 < "CLOSED") And (S2 < "N/A")) Then
If C2 < R2 Then
If Session = "" Then
Session = S2
Else
If Val(Session) Val(S2) Then
Session = S2
End If
End If
End If
End If
If ((S3 < "CLOSED") And (S3 < "N/A")) Then
If C3 < R3 Then
If Session = "" Then
Session = S3
Else
If Val(Session) Val(S3) Then
Session = S3
End If
End If
End If
End If
If ((S4 < "CLOSED") And (S4 < "N/A")) Then
If C4 < R4 Then
If Session = "" Then
Session = S4
Else
If Val(Session) Val(S4) Then
Session = S4
End If
End If
End If
End If
If ((S5 < "CLOSED") And (S5 < "N/A")) Then
If C5 < R5 Then
If Session = "" Then
Session = S5
Else
If Val(Session) Val(S5) Then
Session = S5
End If
End If
End If
End If

CheckSession = Session
End Function



With this Macro, In Excel, if I put a formula at a field
=CheckSession(N6,O6,P6,Q6,R6,N1,O1,P1,Q1,R1)
, it will complaint that Excel cannot calculate a formula...circular
reference...

Then I suspect, the problem might be:

C1 = Application.WorksheetFunction.SumIf(Range("AL5:AL3 807"), "1",
Range("AP5:AP3807"))
C2 = Application.WorksheetFunction.SumIf(Range("AL5:AL3 807"), "2",
Range("AP5:AP3807"))
C3 = Application.WorksheetFunction.SumIf(Range("AL5:AL3 807"), "3",
Range("AP5:AP3807"))
C4 = Application.WorksheetFunction.SumIf(Range("AL5:AL3 807"), "4",
Range("AP5:AP3807"))
C5 = Application.WorksheetFunction.SumIf(Range("AL5:AL3 807"), "5",
Range("AP5:AP3807"))

So, I try to make it C1- C5, a constant value, to see if excel still
complaint cannot calculate formula. Then it turns out OK.

So now, the question is how can I integrate:
C1 = Application.WorksheetFunction.SumIf(Range("AL5:AL3 807"), "1",
Range("AP5:AP3807"))
C2 = Application.WorksheetFunction.SumIf(Range("AL5:AL3 807"), "2",
Range("AP5:AP3807"))
C3 = Application.WorksheetFunction.SumIf(Range("AL5:AL3 807"), "3",
Range("AP5:AP3807"))
C4 = Application.WorksheetFunction.SumIf(Range("AL5:AL3 807"), "4",
Range("AP5:AP3807"))
C5 = Application.WorksheetFunction.SumIf(Range("AL5:AL3 807"), "5",
Range("AP5:AP3807"))

, so that the excel will not complain such.


Anyone ? I have been cracking my head on this. :-( Thanks.