register313

¦^´_ 2# GBKEE

¬O­n¬dªí°Õ!
ª`·NSHEET1»PSHEE2¤§BÄæ¬O¤£¦Pªº
¦bSHEE2 BÄæ¿é¤J±ý¬d¸ß¤§¸ê®Æ,§Y¬d¥X¬Û¹ïÀ³C~JÄ檺¸ê®Æ(¦³3¦C)

¥»ÃD¬O­n¥Îµ{¦¡¼g
¥t¤@ÃD(¦p¤U)¬O¥Î¤½¦¡¼g
sheet-vlookup.zip (4.45 KB)

¤U¸ü¦¸¼Æ: 9

2012-1-17 21:54

register313

¦^´_ 1# tonycho33

1. Sub VLOOKUP()
   
2. '²M°£Sheet2¤§C~JÄæ
   
3. Sheet2.Columns("C:J") = ""
   
4.    '±qSheet2¤§²ÄR¦C(²Ä2¦C)¶}©l
   
5.    R = 2   
   
6.    '°õ¦æ°j°é,ª½¨ìSheet2 R¦CBÄ椧­È=""
   
7.    Do Until Sheet2.Cells(R, "B") = ""
   
8.       'FIND¨ç¼Æ:¦bSheet1.Range("B1:B65535")¤º´M§äSheet2.Cells(R, "B")¤§­È,§ä¨ì®É±N¦ì§}¶Çµ¹S
   
9.       Set S = Sheet1.Range("B1:B65535").Find(Sheet2.Cells(R, "B"), , , xlWhole)
   
10.       '¦bSheet1§ä¨ì¤§¦ì§}¥k°¾²¾1®æ¨ÃÂX¤j¬°3¦C8Äæ ½Æ»s¨ì Sheet2
    
11.       S.Offset(0, 1).Resize(3, 8).Copy Sheet2.Cells(R, "C").Resize(3, 8)
    
12.       '¤U¤@­Ó­n´M§äªº¦C(²Ä5¦C)(²Ä8¦C)...
    
13.       R = R + 3
    
14.    'ªð¦^°j°é
    
15.    Loop
    
16. End Sub

½Æ»s¥N½X

register313

¦^´_ 7# tonycho33

µ{¦¡
sheet-vlookupµ{¦¡.rar (9.3 KB)

¤U¸ü¦¸¼Æ: 8

2012-1-18 14:28

¨ç¼Æ
G2=OFFSET(INDEX(Sheet1!$A:$R,MATCH($A2,Sheet1!$A:$A,0),COLUMN()-1),MOD(ROW()+1,3),0)
G3=OFFSET(INDEX(Sheet1!$A:$R,MATCH($A2,Sheet1!$A:$A,0),COLUMN()-1),MOD(ROW()+1,3),0)
G4=OFFSET(INDEX(Sheet1!$A:$R,MATCH($A2,Sheet1!$A:$A,0),COLUMN()-1),MOD(ROW()+1,3),0)
G2 G3 G4¤½¦¡¬Û¦P

¿ïG2:G4 ¦V¥k©Ô
¦V¤U©Ô

register313

¦^´_ 9# tonycho33

1. Sub VLOOKUP()
   
2. Sheet2.Columns("G:S") = ""   '²M°£Sheet2¤§G~SÄæ
   
3. R = 2   '±qSheet2¤§²ÄR¦C(²Ä2¦C)¶}©l
   
4.    Do Until Sheet2.Cells(R, "A") = ""  '°õ¦æ°j°é,ª½¨ìSheet2 R¦CAÄ椧­È=""
   
5.       Set s = Sheet1.Range("A1:A65535").Find(Sheet2.Cells(R, "A"), , , xlWhole)  'FIND¨ç¼Æ:¦bSheet1.Range("A1:A65535")¤º´M§äSheet2.Cells(R, "A")¤§­È,§ä¨ì®É±N¦ì§}¶Çµ¹S
   
6.           If Not s Is Nothing Then     '·j´M­È§ä¨ì®É¦A§@¼g¤J
   
7.              s.Offset(0, 5).Resize(3, 13).Copy Sheet2.Cells(R, "G").Resize(3, 13)    '¦bSheet1§ä¨ì¤§¦ì§}¥k°¾²¾5®æ¨ÃÂX¤j¬°3¦C13Äæ ½Æ»s¨ì Sheet2
   
8.           End If
   
9.       R = R + 3     '¤U¤@­Ó­n´M§äªº¦C(²Ä5¦C)(²Ä8¦C)...
   
10.    Loop  'ªð¦^°j°é
    
11. End Sub

½Æ»s¥N½X

sheet-vlookupµ{¦¡.rar (9.29 KB)

¤U¸ü¦¸¼Æ: 8

2012-1-19 11:08