question about UDFs

[c1802362]

I wrote this function to count every instance of any cell (column 2)
in a range that starts with "OPEN".

For some reason, the function sums correctly but won't return the sum
(variable "X").

On the excel spreadsheet, the cell where I want the sum is '=findme()'

I've tried some generic UDFs and they work fine, so what am I missing?

Art

**************************************

Function findme() As Integer

Range("A1").Select

x = 0

RowCount = Selection.CurrentRegion.Rows.Count

For i = 2 To RowCount
If Left(ActiveCell.Offset(i - 1, 1), 4) = "OPEN" Then
x = x + 1
End If
Next i

findme = x

End Function

 

[Dave Peterson]

If you meant for this UDF to be called from a cell in a worksheet, you have a
couple of problems.

You can't change selections in UDF's called from a cell in a worksheet.
You probably wouldn't want to rely on the selection.
You'd want to pass the range to inspect to the UDF so excel knows when it has to
recalculate your function.

In fact, that Offset stuff in your code makes it so that excel won't know when
to recalc, too.

If you were running this from a different procedure, you're selecting A1 right
away--and that becomes the only cell in the selection. So if it doesn't start
with "OPEN", you should see 0.

I guess my question boils down to: What do you want to count?

And if this is not a learning experience, you may find using a worksheet
formula:
=countif(b:b,"open*")
easier to use.

But if it has to be done in code (and it's not just a learning experience):

msgbox application.countif(worksheets("somesheet").range("b:B"), "Open*")

may be enough.