CountIF = function not defined ???

[John]

excel vba 6.3 countif gets me a "Function not defined" error. I took the
example straight out of a book. MsgBox = CountIf(Col(1), "*") where
Col(1) is a range array.

I copied some more examples having nothing to do with my program into a
simple macro they all get the same error. Doesn't 6.3 (Excell 2003) have
Countif in it?

John

 

[Gary Keramidas]

try WorksheetFunction.CountIf

 

[John]

thanks... that did it.

Have another problem now. The countif doesn't work with a range I put
together using "union" I took a chance on that.

----
Dim Rows(9) as Range
Set Rows(n) = Union(Cells(n, 1), Cells(n, 3), Cells(n, 5))
----

I'm trying to have a collection or range or array of evey other cell in
a row that i can work with. Is that possible as a range? I tried to read
about collections but just got screwed up.

thanks

John

 

[Gary Keramidas]

not exactly sure what you're trying to accomplish, but:

Option Base 1
Sub test()
Dim arr() As String
Dim i As Long, n As Long

For i = 1 To 18
If i Mod 2 = 1 Then
n = n + 1
ReDim Preserve arr(1 To n)
arr(n) = Cells(1, i).Value
End If
Next

End Sub

 

[Guest]

Countif works that same as it does in a worksheet. In a worksheet it would
not work with a discontiguous range either.

 

[John]

Rats. So for non-contiguous you are back to for-next loop searches loops
instead of built in functions.
John

 

[John]

I Already do that... I was trying to find a neater way.

John