[發問] 如何多個條件搜尋?

winston219

如附檔，謝謝大大! Book21.rar (6.28 KB)

下載次數: 14

2010-12-20 23:10

Hsieh

E2=SUMPRODUCT((Sheet1!$A$2:$A$9=Sheet2!$A2)*(Sheet1!$B$2:$B$9=Sheet2!$B2)*(Sheet1!$C$2:$C$9=Sheet2!$C2)*Sheet1!D$2:D$9)
向下向右複製
VBA方式

1. Sub Ex()
   
2. Set d = CreateObject("Scripting.Dictionary")
   
3. Set d1 = CreateObject("Scripting.Dictionary")
   
4. Dim A As Range
   
5. With Sheet1
   
6. For Each A In .Range(.[A2], .[A2].End(xlDown))
   
7.   mystr = A & A.Offset(, 1) & A.Offset(, 2)
   
8.   d(mystr) = A.Offset(, 3).Value
   
9.   d1(mystr) = A.Offset(, 4).Value
   
10. Next
    
11. End With
    
12. With Sheet2
    
13. For Each A In .Range(.[A2], .[A2].End(xlDown))
    
14.   mystr = A & A.Offset(, 1) & A.Offset(, 2)
    
15.   A.Offset(, 4) = d(mystr)
    
16.   A.Offset(, 5) = d1(mystr)
    
17. Next
    
18. End With
    
19. End Sub

複製代碼

winston219

請問大大如何寫VBA? (因為若以EXCEL函數寫，由於欄位眾多，檔案將會變的很大)

winston219

，感謝Hsieh大大再一次的幫忙!