作者nazomegami (深呼吸~)
看板Office
標題Re: [算表] VBA如何將陣列轉為range
時間Thu Mar 17 21:52:26 2016
各為版大好，以下有一自訂函數，目的是要計算某一項目名稱在某一天時的存貨天數(以
金額加權平均權重)，共計要輸入的8個參數舉例如下：
1.日期 date_range：42401;42416;42431;42446;42461;42476;42491;42506;42521;42536
2.當日日期 cur_date：42536
3.計畫名稱 pjt_range：甲;甲;乙;甲;甲;乙;乙;乙;甲;甲
4.編號 num_range：001;002;001;003;004;002;001;002;001;002
5.序號 srl_range：1;1;1;1;1;1;1;1;1;1
6.項目名稱 item_range：A;A;B;A;A;B;B;B;A;A
7.金額(負數表出售) amount5;5;7;5;5;7;-7;-7;-5;-5
8.目標項目名稱 item_name：A
我的問題是，只要我以下陣列範圍<=27程式都沒有問題，但只要改為28以上時，在求算
ary6(w)時就會有問題，不知原因為何? 還請版上大大解惑，謝謝!
Function inventory_period(date_range As Range, cur_date As Range, _
pjt_range As Range, num_range As Range, srl_range As Range, _
item_range As Range, amount As Range, item_name As Range) _
As Double
Dim date_rangex, cur_datex, pjt_rangex, num_rangex, srl_rangex, _
item_rangex, amountx(27), str1
Dim ary1(27), ary2(27), ary3(27), ary4(27), ary5(27), ary6(27), ary7(27)
Dim i&, w&
date_rangex = date_range
pjt_rangex = pjt_range
num_rangex = num_range
srl_rangex = srl_range
item_rangex = item_range
'將計畫名稱、編號、序號、項目名稱組合成一字串
'計算庫存天數
For i = 0 To UBound(pjt_rangex) - 1
ary1(i) = pjt_rangex(i + 1, 1) & num_rangex(i + 1, 1) & _
srl_rangex(i + 1, 1) & item_rangex(i + 1, 1)
ary2(i) = cur_date - date_rangex(i + 1, 1)
amountx(i) = amount(i + 1, 1)
Next
For i = 0 To 27
If ary1(i) = "" Then ary1(i) = 0
If ary2(i) = "" Then ary2(i) = 0
If amountx(i) = "" Then amountx(i) = 0
Next
'將不重複之組合字串取出
'將不重複組合字串所對應的庫存天數取出
'計算不重複組合字串之累計金額
'計算不重複組合字串之累計庫存天數
For i = 0 To UBound(pjt_rangex) - 1
If InStr(str1, ary1(i)) = 0 Then
str1 = str1 & "," & ary1(i)
ary3(w) = item_rangex(i + 1, 1)
ary4(w) = ary1(i)
ary5(w) = Evaluate("sum(if({""" & Join(ary1, """,""") & """}=""" _
& ary1(i) & """, {" & Join(amountx, ",") & "}))")
ary6(w) = Evaluate("sum(if({""" & Join(ary1, """,""") & """}=""" _
& ary1(i) & """, {" & Join(ary2, ",") & "}))")
w = w + 1
End If
Next
'計算累計金額與累計庫存天數之乘積，所該組合字串已出售(累計金額為0)則乘積為0
For i = 0 To 27
If ary4(i) = "" Then ary4(i) = 0
If ary5(i) = "" Then ary5(i) = 0
If ary6(i) = "" Then ary6(i) = 0
ary7(i) = ary5(i) * ary6(i)
Next
'找出欲求之項目名稱在陣列中之位置i
'求算以該項目名稱為條件之sumproduct(累計金額,累計庫存天數)/sum(累計金額)
i = WorksheetFunction.Match(item_name, ary3, 0) - 1
inventory_period = Evaluate("sum(if({""" & Join(ary3, """,""") & """}=""" _
& ary3(i) & """, {" & Join(ary7, ",") & "}))") _
/ Evaluate("sum(if({""" & Join(ary3, """,""") & """}=""" _
& ary3(i) & """, {" & Join(ary5, ",") & "}))")
End Function
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 114.39.101.64
※ 文章網址: https://www.ptt.cc/bbs/Office/M.1458222748.A.3E8.html
→ nazomegami: 這樣就ok了 03/18 15:52
→ nazomegami: 不過當我把陣列範圍加大到44時又出現錯誤了 03/18 15:53
→ nazomegami: 我有用即時運算去看過程，如下圖 03/18 15:54
→ nazomegami: 我不懂同樣是用"A"做為條件 03/18 15:56
→ nazomegami: 但不一樣的參數範圍，上面那一組卻出現錯誤?? 03/18 15:57
→ soyoso: 和原文類似，用於工作表內可計算但連結成字串用於evaluate 03/18 17:02
→ soyoso: 又出現錯誤，如以range.address的方式帶入evaluate呢 03/18 17:03
→ soyoso: 也可用迴圈來進行判斷及累加數值 03/18 17:04
→ nazomegami: 已排除問題，謝謝你! 03/18 22:27
→ waiter337: 執行階段錯誤 1004 12/12 10:26