標題: [發問] 如何多個條件搜尋? [打印本頁]

---

作者: winston219    時間: 2010-12-20 23:10     標題: 如何多個條件搜尋?

如附檔，謝謝大大![attach]4156[/attach]

---

作者: Hsieh    時間: 2010-12-20 23:25

本帖最後由 Hsieh 於 2010-12-20 23:35 編輯

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    時間: 2010-12-20 23:37

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

---

作者: winston219    時間: 2010-12-20 23:58

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

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)