軟體:excel vba
版本:2010
各位版大好，以下我有一個自訂函數，其中ary2(i,0)為不同字串之組合(projectx、numx
等)，我想要在一個迴圈裡使用sumif，但是在這裡ary2參數似乎是要為range型態，我不想
要在儲存格中多一欄來記錄此一組合字串。
不知道這樣的問題是否有解法? 謝謝!!
Function inventory_period(in_date As Range, cur_date As Range, _
project As Range, num As Range, srl As Range, _
item_name As Range, amount As Range) As Long
Dim in_datex, cur_datex, projectx, numx, srlx, item_namex, amountx, str1
Dim ary1$(), ary2(10, 1), ary3(10)
Dim i&, w&
in_datex = in_date
cur_datex = cur_date
projectx = project
numx = num
srlx = srl
item_namex = item_name
amountx = amount
For i = 1 To UBound(projectx)
ary2(i, 0) = projectx(i, 1) & numx(i, 1) & srlx(i, 1) & item_namex(i, _
1)
ary2(i, 1) = cur_datex - in_datex(i, 1)
Next
For i = 1 To UBound(projectx)
If InStr(str1, ary2(i, 0)) = 0 Then
w = w + 1
str1 = str1 & "," & ary2(i, 0)
ary3(w) = WorksheetFunction.SumIf(ary2, ary2(i, 0), amount)
End If
Next
End Function