How can I integrate SUMIF excel formula into a visual basic function code ?
I'm trying to solve an circular reference issue.

Thanks.

dblAnswer =
Application.WorksheetFunction.SumIf(LookInRange,Lo okForValue,SumRange)

--
Hmm...they have the Internet on COMPUTERS now!


"magix" wrote:

How can I integrate SUMIF excel formula into a visual basic function code ?
I'm trying to solve an circular reference issue.

Thanks.

For SUMIF Formula, I found the following code posted some time ago by JE
McGimpsey helpful:

Dim n As Long
n = Application.WorksheetFunction.SumIf(Columns("A"), "<" & _
"Large Balance", Columns("B"))
MsgBox n



"magix" wrote:

How can I integrate SUMIF excel formula into a visual basic function code ?
I'm trying to solve an circular reference issue.

Thanks.

The excel always complaint about circular references. how can i prevent that
?

I need the value of SUMIF from C1 - C5, in order to compare with R1-R5

My code is as below:


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

CheckSession = Session
End Function






"MDW" wrote in message
...
dblAnswer =
Application.WorksheetFunction.SumIf(LookInRange,Lo okForValue,SumRange)

--
Hmm...they have the Internet on COMPUTERS now!


"magix" wrote:

How can I integrate SUMIF excel formula into a visual basic function code
?
I'm trying to solve an circular reference issue.

Thanks.

I don't see anything in the code you posted that looks like a circular
reference. Is it possible there is another function on your spreadsheet
that's causing the error?

You can suppress the error message by enabling Iteration on Excel. You can
do it manually by going to Tools - Options, and checking the "Iteration"
checkbox. You can also turn it on programmatically with the command
Application.Iteration = True. In most cases, this will make the error "go
away", but it won't fix the underlying problem if there is one.

--
Hmm...they have the Internet on COMPUTERS now!


"magix" wrote:

The excel always complaint about circular references. how can i prevent that
?

I need the value of SUMIF from C1 - C5, in order to compare with R1-R5

My code is as below:


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

CheckSession = Session
End Function






"MDW" wrote in message
...
dblAnswer =
Application.WorksheetFunction.SumIf(LookInRange,Lo okForValue,SumRange)

--
Hmm...they have the Internet on COMPUTERS now!


"magix" wrote:

How can I integrate SUMIF excel formula into a visual basic function code
?
I'm trying to solve an circular reference issue.

Thanks.

when I run this CheckSession ( i.e put this formula in one of the field as
=CheckSession(N6.O6.P6,Q6,R6, N1,O1,P1,Q1,R1)), then I will get following
pop up message:

Microsoft Office Excel cannot calculate a formula. There is a circular
reference in an open workbook, but the references that cause it cannot be
listed for you. Try editing the last formula you entered or removing it with
the Undo command (Edit menu)

in the example below, if I replace C1-C5 with a Constant value, instead of
Application.WorksheetFunction.SumIf, then it will be OK.

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

Please advise, why calculating Application.WorksheetFunction.SumIf will
trigger circular references ?

Magix.


"MDW" wrote in message
...
I don't see anything in the code you posted that looks like a circular
reference. Is it possible there is another function on your spreadsheet
that's causing the error?

You can suppress the error message by enabling Iteration on Excel. You can
do it manually by going to Tools - Options, and checking the "Iteration"
checkbox. You can also turn it on programmatically with the command
Application.Iteration = True. In most cases, this will make the error "go
away", but it won't fix the underlying problem if there is one.

--
Hmm...they have the Internet on COMPUTERS now!


"magix" wrote:

The excel always complaint about circular references. how can i prevent
that
?

I need the value of SUMIF from C1 - C5, in order to compare with R1-R5

My code is as below:


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

CheckSession = Session
End Function






"MDW" wrote in message
...
dblAnswer =
Application.WorksheetFunction.SumIf(LookInRange,Lo okForValue,SumRange)

--
Hmm...they have the Internet on COMPUTERS now!


"magix" wrote:

How can I integrate SUMIF excel formula into a visual basic function
code
?
I'm trying to solve an circular reference issue.

Thanks.