Arrays passed into function

[ChrisHag]

Hi
My VBA is a little rusty, but here goes. I'm trying to write a function that takes two arrays and counts the number of cells with numbers in them according to certain rules. The problem is that whatever I do, a #VALUE error appears in the worksheet cell when I try to run it. My code is below, and the array inputs are meant to be ranges in the worksheet.
Thanks in advance.

Public Function countOwn(id As Variant, ByRef Xarr() As Variant, ByRef Yarr As Variant) As Long

If UBound(Xarr, 1) <> UBound(Yarr, 1) Or UBound(Xarr, 2) > 1 Or UBound(Yarr, 2) > 1 Then
MsgBox "The input arrays must be the same length and of dimension 1"
Exit Function
End If

Dim i As Integer
countOwn = 0
For i = 1 To UBound(Xarr, 1)
If Xarr(i) = id And IsNumeric(Yarr(i)) Then countOwn = countOwn + 1
Next i

End Function

[nathansav]

you need to pass the ranges as type excel.range and for each c in rngX.cells....

you could get this with a formula

[ChrisHag]

Thanks, that works perfectly. The modified code is below.

Public Function countOwn(id As Variant, Xarr As Range, Yarr As Range) As Long

If Xarr.Rows.Count <> Yarr.Rows.Count Or Xarr.Columns.Count > 1 Or Yarr.Columns.Count > 1 Then
MsgBox "The input arrays must be the same length and of dimension 1"
Exit Function
End If

Dim xDat As Variant, yDat As Variant
xDat = Xarr
yDat = Yarr

Dim i As Integer
countOwn = 0
For i = LBound(xDat, 1) To UBound(xDat, 1)
If xDat(i, 1) = id And IsNumeric(yDat(i, 1)) Then countOwn = countOwn + 1
Next i

End Function