標題: [發問] Index and Match VBA [打印本頁] 作者: mdr0465 時間: 2020-6-24 23:32 標題: Index and Match VBA
各位大大
如果我"庫存"里在A欄和AG欄 兩個合符的條件的情況下在尋找"WORKING" 找回相對應的資料,
我應點寫VBA CODE 呢???
謝謝作者: 准提部林 時間: 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
