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