[µo°Ý] excel ¨ç¼ÆÀ³¥Î¦bVBA ªº°ÝÃD

hugh0620

¦U¦ì¤j¤j

      ¤p§Ì¤@ª½¹ïexcel ¨ç¼ÆÀ³¥Î¦bvba¤Wªº§Þ¥©¤@ª½¤£¬O«Ü¼ô½m
      ¬O§_¥i¥H±Ð¾É§Ú¤@¤U~
         ¹³¬O¤U­±ªº¦bvba¨Ï¥Îvlookup³o­Ó¨ç¼Æ
      ¦bvba¨Ï¥Î¨ç¼Æ­n¨Ï¥ÎApplication.WorksheetFunction,¦A¥[¤W­n¥Î¤§¨ç¼Æ
      §Ú¬O­n¥Îvlookup³o­Ó¨ç¼Æ
      §Ú´N¥´¤FApplication.WorksheetFunction.vlookup(arg1,arg2,arg3,[arg4])

        ¤U¦C¬O§Úªºµ{¦¡½X
      wf = Application.WorksheetFunction.VLookup(Sheet1.Cells(5, 9) & Sheet1.Cells(5, 10) & Sheet1.Cells(5, 14), Sheet2.Range("A4:I1000"), 9, False)
         Sheet1.Cells(5, 46) = wf
         
         ¦ýµLªk°õ¦æ
      °ÝÃD1:¦³Ãö¨ç¼Æ¦bvbaªºÀ³¥Î¬O§_¥i¥H´£¨Ñ¤@­Ó°ò¥»ªº¼¶¼gÅÞ¿è (¥i§_´£¨Ñ¤@­Ó½d¨Ò)
         °ÝÃD2: vlookup(arg1,arg2,arg3,[arg4]) ¤¤ªºarg¬O¤°»ò·N«ä,§Ú¼¶¼gªº¦a¤è­þÃäµo¥Í°ÝÃD

oobird

»yªk
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
§Aªºlookup_value¬°Sheet1.Cells(5, 9)& Sheet1.Cells(5, 10) & Sheet1.Cells(5, 14)¤T­ÓÀx¦s®æ¦X¦}°_¨Óªº¦r²Å¡Atable_arrayªº²Ä¤@Äæ´N­n¦³»P³o­Ó¦r²Å¬Û²Åªº¤º®e¡A¤£¥i¥H¤À¶}¤T­ÓÄæ¦ì©ñ¸m¡C

hugh0620

»yªk
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
§Aªºlookup_value¬°Sheet1.Cells(5 ...
oobird µoªí©ó 2010-11-22 12:38

    ¤j¤j§AÁ¿ªº³o­Ó§Ú²M·¡,¦bexcel ¨ç¼Æ¨Ï¥Î¤W¨S¦³°ÝÃD
   °ÝÃD¦b©ó¼g¦bVBA¤W®É,Vlookup³o­Ó¨ç¼Æµ{¦¡½X¤W¸Ó¦p¦ó¼¶¼g

oobird

´N¦p¤@¼Óªº¼gªk¨S¿ùªº¡C¬Ý¨ì¤G¼Óªº¬õ¦r¶Ü¡H§A´N¤Ö­Ó³s±µ²Å¡C

hugh0620

¤j¤j~ ±z¬O¹ïªº~ ¦ý§Ú«o¤S©µ¦ù¥X¤@­Ó°ÝÃD

·í§Úµ{¦¡½X¦b¶]ªº®É­Ô,¦pªG¹J¨ìvlookup§ì¤£¨ì¸ê®Æ®É,¥X²{#N/A®É
µ{¦¡´N·|°±¤î,½Ð°Ý¤j¤j¸Ó¦p¦ó³B²z

¦b¨ç¼Æ¤¤¬O¤ñ¸û¦n³B²z´N¥Îif(iserror(vlookup(lookup_value,table_array,col_index_num,range_lookup),"",lookup_value,table_array,col_index_num,range_lookup)) ´N¥i¥H±N§ì¤£¨ì¸ê®Æªº³¡¥÷,Åܦ¨ªÅ®æ

­Y¬O§Ú­n¦bvba¤W³B²z,¸Ó¦p¦ó°µ©O??  ½Ð¤j¤j«ü¾É¤@¤U

¦bvba¤§«eªº³B²z¤è¦¡,¦p¤U

   Sheet1.Cells(4 + x, 48).Formula = "=IF(ISERROR(VLOOKUP(RC[-39]&RC[-38]&RC[-34],SBD!R3C1:R10000C9,9,FALSE)),"""",VLOOKUP(RC[-39]&RC[-38]&RC[-34],SBD!R3C1:R10000C9,9,FALSE))"
    Sheet1.Cells(4 + x, 48) = Sheet1.Cells(4 + x, 48)  <---¥D­n¬O¤£·QÅý¬¡­¶¤W±a¥X¤½¦¡
¦ý¬O³o¼Ëªº³B²z¤è¦¡,Åý§Ú¦b°õ¦æ®É¶]°_¨Ó­nµ¥«Ý«Ü¤[ªº®É¶¡

Hsieh

¦^´_ 5# hugh0620


If IsError(Application.VLookup(Sheet1.Cells(5, 9) & Sheet1.Cells(5, 10) & Sheet1.Cells(5, 14), Sheet2.Range("A4:I1000"), 9, False)) Then
wf = ""
Else
wf = Application.VLookup(Sheet1.Cells(5, 9) & Sheet1.Cells(5, 10) & Sheet1.Cells(5, 14), Sheet2.Range("A4:I1000"), 9, False)
End If

hugh0620

·PÁ¦U¦ì¤j¤j~ °ÝÃD¤w¸g¸Ñ¨M¤F~

¥u¬O¦]¬°§Úªº¸ê®Æ¸û¤j,³o¼Ë³B²zªº¶D«×¦³ÂIºC¦Ó¤w~

oobird

vba¦³vbaªº¤èªk¡A§A¦bvba¤¤«j±j¨Ï¥Î¤u§@ªí¨ç¼Æ³t«×´N¤£¯à­ÝÅU¤F¡I

hugh0620

¦^´_ 6# Hsieh


    ½Ð°Ý¤j¤j¤@­Ó°ÝÃD,¤j¤jªº¼gªk¬O:Application.VLookup  ¬°¦ó¤£¬OApplication.WorksheetFuncation.VLookup  
                             ¦]¬°§Ú¦b½s¼gµ{¦¡½Xªº®É­Ô,­n½s¼gApplication.WorksheetFuncationªº®É­Ô,¦bÂIªº®É­Ô¤~·|¥X²{¨ç¼Æªº¿ï³æ
                   ³o¨âªÌ¶¡ªº®t²§¦b­þ©O??    ½Ð¤j¤j±Ð¾É¤@¤U,À°¾Ç²ß¤¤ªº§Ú¸Ñ´b
                   ps.§Ú½s¼gªº¬OWorksheetFuncation.¨ç¼Æ,¥i¬O«o·|²£¥Í¿ù»~
          If IsError(Application.VLookup(Sheet1.Cells(4 + X, 9) & Sheet1.Cells(4 + X, 10) & Sheet1.Cells(4 + X, 14), Sheet3.Range("A4:I15000"), 9, False))