Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating formula ..circular references
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating formula ..circular references
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Circular References | Excel Worksheet Functions | |||
Circular References | Excel Discussion (Misc queries) | |||
Those Circular References... | Excel Discussion (Misc queries) | |||
Help :Circular References | Excel Worksheet Functions | |||
Circular References | Excel Programming |