I am trying to count how many in a range meet a condition. The twist
is that there is another column that the column needs to meet the
condition. I wanted it as a Function so I could show my work to
others who may have to view this. It calculates fine but then seems
to be stuck in a loop on recalculating. Any Help? Code below.

Thanks,
Jay

Function CountCondition(CntRng As Range, Cond As Double) As Variant

Dim X As Integer
Dim i
X = 0
For Each i In CntRng
If i.Offset(0, -4).Value = Cond And Right(i.Value, 4) = "2008"
Then
X = X + 1
End If
CountCondition = X
Next i

End Function

Works fine for me, what do you mean by re-calculating?

"jlclyde" wrote:

I am trying to count how many in a range meet a condition. The twist
is that there is another column that the column needs to meet the
condition. I wanted it as a Function so I could show my work to
others who may have to view this. It calculates fine but then seems
to be stuck in a loop on recalculating. Any Help? Code below.

Thanks,
Jay

Function CountCondition(CntRng As Range, Cond As Double) As Variant

Dim X As Integer
Dim i
X = 0
For Each i In CntRng
If i.Offset(0, -4).Value = Cond And Right(i.Value, 4) = "2008"
Then
X = X + 1
End If
CountCondition = X
Next i

End Function

On Oct 24, 8:35*am, Mike H wrote:
Works fine for me, what do you mean by re-calculating?



"jlclyde" wrote:
I am trying to count how many in a range meet a condition. *The twist
is that there is another column that the column needs to meet the
condition. *I wanted it as a Function so I could show my work to
others who may have to view this. *It calculates fine but then seems
to be stuck in a loop on recalculating. *Any Help? *Code below.

Thanks,
Jay

Function CountCondition(CntRng As Range, Cond As Double) As Variant

Dim X As Integer
Dim i
X = 0
For Each i In CntRng
* * If i.Offset(0, -4).Value = Cond And Right(i.Value, 4) = "2008"
Then
* * * * X = X + 1
* * End If
CountCondition = X
Next i

End Function- Hide quoted text -

- Show quoted text -

It shows Calculating in the lower left corner 0% and it never
increases and then I need to hit Ctrl + Break to get out of the loop.

On Oct 24, 8:35*am, Mike H wrote:
Works fine for me, what do you mean by re-calculating?



"jlclyde" wrote:
I am trying to count how many in a range meet a condition. *The twist
is that there is another column that the column needs to meet the
condition. *I wanted it as a Function so I could show my work to
others who may have to view this. *It calculates fine but then seems
to be stuck in a loop on recalculating. *Any Help? *Code below.

Thanks,
Jay

Function CountCondition(CntRng As Range, Cond As Double) As Variant

Dim X As Integer
Dim i
X = 0
For Each i In CntRng
* * If i.Offset(0, -4).Value = Cond And Right(i.Value, 4) = "2008"
Then
* * * * X = X + 1
* * End If
CountCondition = X
Next i

End Function- Hide quoted text -

- Show quoted text -

I ahve a soloution, but I am hoping that you can tell me why this is
tuck in a loop so I can avoid dong this again. The soloution is to
use Sumproduct and a helper column for the Right(i.value,4).

Thanks,
Jay

Hi,

I can see no reason at all why your UDF would go into and endless loop so
suspect that when the UDF calculates so do other formula on the sheet and
this is causing your problem.

Mike


"jlclyde" wrote:

On Oct 24, 8:35 am, Mike H wrote:
Works fine for me, what do you mean by re-calculating?



"jlclyde" wrote:
I am trying to count how many in a range meet a condition. The twist
is that there is another column that the column needs to meet the
condition. I wanted it as a Function so I could show my work to
others who may have to view this. It calculates fine but then seems
to be stuck in a loop on recalculating. Any Help? Code below.

Thanks,
Jay

Function CountCondition(CntRng As Range, Cond As Double) As Variant

Dim X As Integer
Dim i
X = 0
For Each i In CntRng
If i.Offset(0, -4).Value = Cond And Right(i.Value, 4) = "2008"
Then
X = X + 1
End If
CountCondition = X
Next i

End Function- Hide quoted text -

- Show quoted text -

I ahve a soloution, but I am hoping that you can tell me why this is
tuck in a loop so I can avoid dong this again. The soloution is to
use Sumproduct and a helper column for the Right(i.value,4).

Thanks,
Jay