Input range to UDF ?
 

My function is

Function nEven(mRange As Range) As Long
Dim cell As Range, score As Integer
For Each cell In mRange
score = cell.Value
If score 10 Then
nEven = nEven + 1
End If
Next cell
End Function

In the worksheet the cells A1 down to C4 have
12 0 0
0 13 0
0 0 45
0 3 0

now then =nEven(A1:C4) works, but =nEven(A1,B2,C3) with three separate cells
does not.

Looking at the second equation, I see that Excel would have trouble telling
the difference a single range with three cells and three separate arguments.

Thanks for any help you can give me.
--
jake

Input range to UDF ?
 

in the VBA help for function, look at the information for a paramarray.


ParamArray Optional. Used only as the last argument in arglist to indicate
that the final argument is an Optional array of Variant elements. The
ParamArray keyword allows you to provide an arbitrary number of arguments. It
may not be used with ByVal, ByRef, or Optional.

--
Regards,
Tom Ogilvy


"Jakobshavn Isbrae" wrote:

My function is

Function nEven(mRange As Range) As Long
Dim cell As Range, score As Integer
For Each cell In mRange
score = cell.Value
If score 10 Then
nEven = nEven + 1
End If
Next cell
End Function

In the worksheet the cells A1 down to C4 have
12 0 0
0 13 0
0 0 45
0 3 0

now then =nEven(A1:C4) works, but =nEven(A1,B2,C3) with three separate cells
does not.

Looking at the second equation, I see that Excel would have trouble telling
the difference a single range with three cells and three separate arguments.

Thanks for any help you can give me.
--
jake

Input range to UDF ?
 

Function nEven(ParamArray mRange()) As Long
Dim cell As Range, score As Integer
Dim i As Long
For i = LBound(mRange) To UBound(mRange)
If TypeOf mRange(i) Is Range Then
For Each cell In mRange(i)
If cell.Value 10 Then nEven = nEven + 1
Next cell
End If
Next i
End Function



--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Jakobshavn Isbrae" wrote in
message ...
My function is

Function nEven(mRange As Range) As Long
Dim cell As Range, score As Integer
For Each cell In mRange
score = cell.Value
If score 10 Then
nEven = nEven + 1
End If
Next cell
End Function

In the worksheet the cells A1 down to C4 have
12 0 0
0 13 0
0 0 45
0 3 0

now then =nEven(A1:C4) works, but =nEven(A1,B2,C3) with three separate
cells
does not.

Looking at the second equation, I see that Excel would have trouble
telling
the difference a single range with three cells and three separate
arguments.

Thanks for any help you can give me.
--
jake

Input range to UDF ?
 

Thank you Bob & Tom. I can't believe how fast you guys are.
--
jake


"Bob Phillips" wrote:

Function nEven(ParamArray mRange()) As Long
Dim cell As Range, score As Integer
Dim i As Long
For i = LBound(mRange) To UBound(mRange)
If TypeOf mRange(i) Is Range Then
For Each cell In mRange(i)
If cell.Value 10 Then nEven = nEven + 1
Next cell
End If
Next i
End Function



--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Jakobshavn Isbrae" wrote in
message ...
My function is

Function nEven(mRange As Range) As Long
Dim cell As Range, score As Integer
For Each cell In mRange
score = cell.Value
If score 10 Then
nEven = nEven + 1
End If
Next cell
End Function

In the worksheet the cells A1 down to C4 have
12 0 0
0 13 0
0 0 45
0 3 0

now then =nEven(A1:C4) works, but =nEven(A1,B2,C3) with three separate
cells
does not.

Looking at the second equation, I see that Excel would have trouble
telling
the difference a single range with three cells and three separate
arguments.

Thanks for any help you can give me.
--
jake