Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
List Box - For Input Range can I use named range in another workbo | Excel Worksheet Functions | |||
Input range | Charts and Charting in Excel | |||
Input box range - output range | Excel Programming | |||
CODE to select range based on User Input or Value of Input Field | Excel Programming | |||
Macro to input formula in range based on another range | Excel Programming |