Calculating formula ..circular references

[magix]

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:AL3807"), "1",
Range("AP5:AP3807"))
C2 = Application.WorksheetFunction.SumIf(Range("AL5:AL3807"), "2",
Range("AP5:AP3807"))
C3 = Application.WorksheetFunction.SumIf(Range("AL5:AL3807"), "3",
Range("AP5:AP3807"))
C4 = Application.WorksheetFunction.SumIf(Range("AL5:AL3807"), "4",
Range("AP5:AP3807"))
C5 = Application.WorksheetFunction.SumIf(Range("AL5:AL3807"), "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:AL3807"), "1",
Range("AP5:AP3807"))
C2 = Application.WorksheetFunction.SumIf(Range("AL5:AL3807"), "2",
Range("AP5:AP3807"))
C3 = Application.WorksheetFunction.SumIf(Range("AL5:AL3807"), "3",
Range("AP5:AP3807"))
C4 = Application.WorksheetFunction.SumIf(Range("AL5:AL3807"), "4",
Range("AP5:AP3807"))
C5 = Application.WorksheetFunction.SumIf(Range("AL5:AL3807"), "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:AL3807"), "1",
Range("AP5:AP3807"))
C2 = Application.WorksheetFunction.SumIf(Range("AL5:AL3807"), "2",
Range("AP5:AP3807"))
C3 = Application.WorksheetFunction.SumIf(Range("AL5:AL3807"), "3",
Range("AP5:AP3807"))
C4 = Application.WorksheetFunction.SumIf(Range("AL5:AL3807"), "4",
Range("AP5:AP3807"))
C5 = Application.WorksheetFunction.SumIf(Range("AL5:AL3807"), "5",
Range("AP5:AP3807"))

, so that the excel will not complain such.


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

 

[merjet]

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

Where did you put this formula? If it's in any of the 10 cells used
as arguments or any cell that *depends on* any of the 10, a circular
reference error will result. However, if you try the same thing a 2nd
time, the popup message may not occur and the circular reference error
is evident only in Status Bar. I suspect this is what's happening to
you when you make C1-C5 constants. You think there is no longer a
circular reference because you are not seeing the popup. You can only
be sure there is no circular reference when the Status Bar is "clean".

Hth,
Merjet