I want to be understand use paramarray
This is my problem
In (a1:a20) contain date,string,numeric
I want use udf paramarray to sum.count of date,string,numeric
While i am use function in b1 and drag to d1
Function test(paramarray x()) Dim r ,i& For each r in x() If isdate(r.value2) = true then s= s & "," & r.value2 Else if isnumeric(r.value) then s=s&","&r.value Else s =s& ","&r.value End if Next r Test=ubound(s) End funtion
In b1=.count (date),c1=.count(string) and d1=.count(numeric)
Hi,
What makes you think Paramarrays are relevant here?
Don
Please remember to mark your thread 'Solved' when appropriate.
Hi xlnitwit forgive me,i dont know how to use paramarray
I want you explain me,give me example thankyou
Besides parramarray, you can't evaluate date using Value2.
A Paramarray is used when you don't know how many parameters will be passed. Think of the function SUMIFS which can have a variable number of criteria pairs passed to it after the range to sum.
It sounds to me as though you want to return an array, not pass one.
Thank you jindon
Xlnitwit
Give me sample problem
B1Originally Posted by daboho
=IFERROR(DataTypes($A1:$A20,COLUMN(A1)),"")
fill right
Function DataTypes(rng As Range, ref As Long) Dim r As Range, x As String With CreateObject("Scripting.Dictionary") For Each r In rng x = TypeName(r.Value) .Item(x) = .Item(x) + 1 Next DataTypes = Join(Array(.keys()(ref - 1), .items()(ref - 1)), ":") End With End Function
Here is a minimal version of a SumIfs function
which you can call with varying numbers of criteria, such asFunction MySumIfs(sumrange As Range, ParamArray criteriapairs()) As Double Dim n As Long Dim index As Long Dim bMatch As Boolean ' loop through sum range For index = 1 To sumrange.Cells.Count bMatch = True ' loop through criteria pairs (range and criteria to test for) For n = LBound(criteriapairs) To UBound(criteriapairs) Step 2 ' if doesn't match, set variable to false and skip the rest of the checks If criteriapairs(n).Cells(index) <> criteriapairs(n + 1) Then bMatch = False Exit For End If Next n ' only add the value if all criteria met If bMatch Then MySumIfs = MySumIfs + sumrange.Cells(index) Next index End Function
=MySumIfs($C$1:$C$15,$A$1:$A$15,"Item1")
or
=MySumIfs($C$1:$C$15,$A$1:$A$15,"Item1",$B$1:$B$15,"test1")
like the worksheet function.
Thankyou jindon and xlnitwit
You're welcome. Please remember to mark the thread Solved using the Thread Tools link at the top of the page.
Xlnitwit my handphone not have fasilitas solved bellow my picture
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks