¨âµ§¸ê®Æ¶¡±µ¤ñ¹ï(¨Ï¥Îvlookup)

01300607

§Ú¦³¨âµ§¸ê®Æ­n¤ñ¹ï¡A¥i¬O§Ú­n¤ñ¸ûªºÄæ¦ì¸ê®Æ¨Ã¤£§¹¥þ¬Û¦P¡A¨Ò¦p¡A¦bA¤u§@ªí´ò¤H¶¤¥HLakersªí¥Ü¡A¦ý¦bB¤u§@ªí´ò¤H¶¤¬O¥HLALªí¥Ü
¦]¦¹§Ú·Q§Q¥Îvlookupªº¤è¦¡¶¡±µ¤ñ¸û¸ê®Æ
¥H¤U¬O§ÚªºVBAµ{¦¡½X¡A¥i¬O°õ¦æ¤W«o¦³¿ù»~¡A½Ð°Ý§Ú³o¼Ë¨Ï¥Î¹ï¶Ü?¥i¥H³o¼Ë¥Î¶Ü?¦³¨ä¥L¤èªk¯à¹F¦¨§Úªº¥Øªº¶Ü?ÁÂÁÂ
Sub aa()

Dim mDic As Object
Dim mWk1 As Workbook
Dim mSht1 As Worksheet
Dim mRng As Range
Dim vRng As Range
Dim E As Range

Set mDic = CreateObject("Scripting.Dictionary")
Set mWk1 = Workbooks("Mapping1")
With mWk1
    Set mSht1 = .Worksheets(2)
    With mSht1
        Set mRng = .Range("b2:b" & .[b65536].End(xlUp).Row)
    End With
    Set vRng = .Worksheets(3).Range("a2:b11")
    For Each E In mRng
        If mDic.Exists(E.Value) = False Then
          mDic(Application.WorksheetFunction.vlookup(E.Value, vRng, 2, 0) & Application.WorksheetFunction.vlookup(E.Offset(, 2).Value, vRng, 2, 0)) = E.Offset(, 4).Resize(,2)
        End If
    Next
End With

    With Worksheets(1)

        For Each E In .Range(.[g2], .[g2].End(xlDown))

            E.Offset(, 8).Resize(, 2) = mDic(E.Value & E.Offset(, 7).Value)

        Next

    End With

End Sub

Hsieh

¦^´_ 1# 01300607


    §A¦Û¤v³£»¡°õ¦æ¥X²{¿ù»~¤F¡AÁٰݳo¼Ë¥i¥H¶Ü?³o¤£¬O«Ü¥Ù¬Þ¶Ü?
¤W¶Ç§AªºÀɮ׬ݬÝ

01300607

Map.zip (12.2 KB)

¤U¸ü¦¸¼Æ: 14

2013-3-19 23:03

¤£¦n·N«ä¡A³o¬O§ÚªºÀÉ®×
§Ú¥u·Qª¾¹D¬O¦³®Ú¥»¤Wªº¿ù»~¡AÁÙ¬O¤@¨Ç¨ä¥L¤ñ¸û¤pªº¦a¤è©Ò³y¦¨ªº¿ù»~
ÁÂÁ¤j®aÀ°¦£

Hsieh

¦^´_ 3# 01300607
¬Ý¤£À´§A­n¤ñ¹ï¬Æ»ò?
·Ó²z»¡À³¸ÓSheet3¬O¹ï·Óªí¡A¦ý¬Oµ{¦¡½X«o«D¥H¦¹¬°¹ï·Óªí
ÁÙ¬O­n»¡©ú²M·¡§Aªº¹ï·Ó¼Ð·Ç¡A¤~¯àª¾¹D¦p¦ó­×¥¿¡C

01300607

¹ï¤£°_¡A§Úªºªí¹F¯à¤O¦³ÂI®t
§Ú­n¤ñ¹ïsheet1¸òsheet2ªº¸ê®Æ¡A¥i¬O­n¤ñ¹ïªº¶µ¥Ø¨Ã«D§¹¥þ¬Û¦P¡A
©Ò¥H§Ú±Nsheet2ªº¸ê®Æ¥Hsheet3¬°¹ï·Óªí¡A¦A¸òsheet1ªº¸ê®Æ¶i¦æ¤ñ¹ï¡A
³Ì«á¤ñ¹ï¥X¨Ó§âsheet2ªº¸ê®Æ¶ñ¨ìsheet1
¥i¯à§Úªºµ{¦¡½X¦³ÂI°ÝÃD¡A¤~Åý±z¬Ý¤£À´
ÁÂÁª©¥DÀ°¦£

01300607

§Ú§ä¥X¿ù»~¤F¡A­ì¨Ó¬Ovlookup function ¤ñ¹ï¥X²{error©Ò¥H¾ã­Óµ{¦¡¤~¥X²{°ÝÃD
·PÁª©¥D

Hsieh

¦^´_ 6# 01300607
¤£¬O«Ü²M·¡§Aªº¤ñ¹ï¼Ò¦¡
¸Õ¸Õ¬Ýµ²ªG¹ï¤£¹ï

1. Sub ex()
   
2. Dim A As Range, d As Object, d1 As Object
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. Set d1 = CreateObject("Scripting.Dictionary")
   
5. 
   
6. With Sheet3 '¼g¤J¹ï·Óªí
   
7.    For Each A In .Range(.[A2], .[A2].End(xlDown))
   
8.    d(A.Value) = A.Offset(, 1)
   
9.    Next
   
10. End With
    
11. With Sheet2  '¥H¹ï·Ó¦WºÙ¦s¤J¼Æ­È
    
12.    For Each A In .Range(.[B2], .[B2].End(xlDown))
    
13.    d1(d(A.Value)) = Array(A.Offset(, 4), A.Offset(, 5))
    
14.    Next
    
15. End With
    
16. With Sheet1 '±N¹ï·Ó¼Æ­È¼g¤J
    
17.    For Each A In .Range(.[G2], .[G2].End(xlDown))
    
18.    A.Offset(, 8).Resize(, 2) = d1(d(A.Value))
    
19.    Next
    
20. End With
    
21. End Sub

½Æ»s¥N½X

01300607

ªO¥Dªº¤è¦¡§Ú¤§«e³£¨S·Q¹L¡A¨Ì·ÓªO¥Dªº¤è¦¡¤]¯à¹F¨ì§Ú­nªº®ÄªG¡A¦Ó¥B¤£¥Î¨Ï¥ÎVlookup function
¥H¤U¬O§Úµy·L­×§ï¹Lªº¡A°õ¦æ«á¤]¯à¹F¨ì§Ú·Q­nªº¥Øªº

1. Sub ex()
   
2. Dim A As Range, d As Object, d1 As Object
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. Set d1 = CreateObject("Scripting.Dictionary")
   
5. 
   
6. With Worksheets(3)
   
7.    For Each A In .Range(.[A2], .[A2].End(xlDown))
   
8.    d(A.Value) = A.Offset(, 1)
   
9.    Next
   
10. End With
    
11. With Worksheets(2)
    
12.    For Each A In .Range(.[B2], .[B2].End(xlDown))
    
13.    d1(d(A.Value) & d(A.Offset(, 2).Value)) = Array(A.Offset(, 4), A.Offset(, 5))
    
14.    Next
    
15. End With
    
16. With Worksheets(1)
    
17.    For Each A In .Range(.[G2], .[G2].End(xlDown))
    
18.    A.Offset(, 8).Resize(, 2) = d1(A.Value & A.Offset(, 7).Value)
    
19.    Next
    
20. End With
    
21. End Sub

½Æ»s¥N½X

·PÁªO¥DÀ°¦£¡A¤S¾Ç¨ì¤F¡AÁÂÁÂ