[µo°Ý] ¿z¿ï³]¤½¦¡ªº¥¨¶°

adam2010

½Ð±Ð¦U¦ì°ª¤â¡A§Ú»Ý­n±NTOP¨t¦C²£«~ªº¥æ´Á¥t¥~Vlookup¨ì­ì¨Óªº¸ê®Æ¤º¡A§Ú¬O¥Î¤gªk¿ý»sªº¤è¦¡

Selection.AutoFilter    '¿z¿ï   
    ActiveSheet.Range("$A$1:$I$305").AutoFilter Field:=1, Criteria1:=Array( _
        "TOP*"), Operator:=xlFilterValues              '¿z¿ï¥X©Ò¦³TOP²£«~   
    Range("E6").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],WIP!C[-3]:C[4],8,0)"
    Range("E6").Select                       '±N²Ä¤@­ÓTOP¥æ´Á³]©wVlookup¤½¦¡   
    Selection.FillDown        '±N¤½¦¡¤U©Ô½Æ»s¨ì©Ò¦³¿z¿ï¥X¨ÓªºÄæ¦ì   
    Selection.AutoFilter         '±N¿z¿ï¨ú®ø

¦ý¬O...
1.¨C¦¸¿z¿ï«á²Ä¤@­ÓTOP²£«~ªº¦ì¸m¤£·|©T©w¦bE6
2.°õ¦æ²Ä¤G¦¸®ÉSelection.FillDownµL®Ä
½Ð¦U¦ì°ª¤â«üÂI¡AÁÂÁ¡I
ESOD_°ÝÃD0906.zip (202.25 KB)

¤U¸ü¦¸¼Æ: 8

2013-9-6 22:01

adam2010

½Ð°Ý¦³¨º¦ì¤j¤j¥i¥H¨ó§U¶Ü¡HÁÂÁ¡I

Hsieh

¦^´_ 2# adam2010

1. Sub ¥¨¶°2()
   
2.     '¿z¿ï¥X©Ò¦³TOP²£«~
   
3.     ActiveSheet.Range("$A$1:$I$305").AutoFilter Field:=1, Criteria1:=Array( _
   
4.         "TOP*"), Operator:=xlFilterValues
   
5.     '±N¿z¿ïµ²ªG¥æ´Á³]©wVlookup¤½¦¡
   
6.     Range([E2], [A65536].End(xlUp).Offset(, 4)).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=VLOOKUP(RC[-3],WIP!C[-3]:C[4],8,0)"
   
7.     '±N¿z¿ï¨ú®ø
   
8.     ActiveSheet.Range("$A$1:$I$305").AutoFilter
   
9. End Sub

½Æ»s¥N½X

adam2010

­ì¨Ó¬O§Úªº°ÝÃD©ñ¿ùª©¤F¡A¯u¬O·PÁ GBKEE±N°ÝÃD²¾¹L¨Ó
·PÁÂHsieh¤jªº¸Ñµª¡A­è¤~¤p¸Õ¤F¤@¤U¡AOK! ÁÂÁ¡I