What is wrong with my UDF

  • Thread starter Thread starter jlclyde
  • Start date Start date
J

jlclyde

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?







- Show quoted text -
Click to expand...

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.
 
Works fine for me, what do you mean by re-calculating?







- Show quoted text -
Click to expand...

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
 
Back
Top