SUMIF

[magix]

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

Thanks.

 

[Guest]

dblAnswer =
Application.WorksheetFunction.SumIf(LookInRange,LookForValue,SumRange)

 

[Guest]

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]

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

CheckSession = Session
End Function

 

[Guest]

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.

 

[magix]

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.

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

Magix.