- 帖子
- 2839
- 主題
- 10
- 精華
- 0
- 積分
- 2895
- 點名
- 0
- 作業系統
- 〔略〕
- 軟體版本
- 〔略〕
- 閱讀權限
- 100
- 性別
- 男
- 來自
- 〔略〕
- 註冊時間
- 2013-5-13
- 最後登錄
- 2024-12-18
|
2#
發表於 2020-6-25 11:37
| 只看該作者
雙條件--用index+match--行不通:
Sub 載入庫存()
Dim R&, C&, i&, j%, Arr, Brr, Crr, xD, U&
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([working!AF1], [working!A1].Cells(Rows.Count, 1).End(xlUp))
For i = 2 To UBound(Arr)
xD(Arr(i, 1) & Arr(i, UBound(Arr, 2))) = i
Next i
'----------------------------------
Brr = Range([庫存!AG1], [庫存!B1].Cells(Rows.Count, 1).End(xlUp))
ReDim Crr(1 To UBound(Brr), 1 To UBound(Brr, 2) - 2)
For i = 5 To UBound(Brr)
U = Val(xD(Brr(i, 1) & Brr(i, UBound(Brr, 2))))
If U = 0 Then GoTo 101
For j = 1 To UBound(Crr, 2)
Crr(i - 4, j) = Arr(U, j + 1)
Next j
101: Next i
'----------------------------------
[庫存!C5:AF5].Resize(UBound(Crr)) = Crr
End Sub
 Xl0000210(庫存表更新).rar (85.48 KB)
======================== |
|
發表於 2020-6-24 23:32
| 
