作者nazomegami (深呼吸~)
看板Office
标题[算表] VBA如何将阵列转为range
时间Tue Mar 15 22:00:26 2016
软体: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
--
※ 发信站: 批踢踢实业坊(ptt.cc), 来自: 114.39.49.112
※ 文章网址: https://webptt.com/cn.aspx?n=bbs/Office/M.1458050428.A.3CD.html
1F：→ soyoso: 用evaluate试试 03/15 23:10
2F：→ nazomegami: 我令变数a=Evaluate("{""" & Join(ary2, """;""") & " 03/16 09:09
3F：→ nazomegami: ""}") 03/16 09:09
4F：→ nazomegami: 并将sumif函数中的ary2以a变数来取代 03/16 09:10
5F：→ nazomegami: 但还是失败了 03/16 09:10
6F：→ nazomegami: 但是a有成功转变为与amount一样的格式 03/16 09:12
7F：→ soyoso: 想到的是用evaluate用於sumif上 03/16 11:06
8F：→ soyoso: 因带入function为range型态是否改用sumifs的方式加总 03/16 11:25
9F：→ nazomegami: 可能要用evaluate(sum(if...的方式 03/16 17:56
10F：→ nazomegami: 我再试试 03/16 17:56
11F：→ soyoso: 抱歉是用於sum(if上，如原po先前VBA阵列运算问题(SUMIF)的 03/16 18:36
12F：→ soyoso: 回文方式 03/16 18:36
13F：→ nazomegami: 已测试成功，谢谢! 03/17 17:05