½Ð±Ðvbaªº¼¶¼g°ÝÃD

lionliu

¦U¦ì¤j­ô¦n
¤p§Ì¸ÕµÛ¼g¥X¤U¦Cµ{¦¡¦ý¬O·|¦³°ÝÃD¡A½Ð±Ð­n¦p¦ó¸Ñ
²Ä9¦æ·|¥N¥X¤½¦¡¡A¦ý¬O¥N¤£¥X¸ê®Æ¡C
²Ä10¦æ·|¥X¿ù¡C

1.  Sub trnprg()
2.  Dim myrow As Long
3.  Dim myrange As Range
4  Dim i As Integer
5  myrow = Range("a1").End(xlDown).Row
6 For i = 2 To myrow
7    Set myrange = Cells(i, 1)
              
8       If myrange <> Empty Then
9             Cells(i, 2).FormulaR1C1 = "=VLOOKUP(CELLS(1,I),Jsc_Data!A:G,6,FALSE)"
10          Cells(i, 5).FormulaR1C1 = "=CONCATENATE(""¥N¹Ô´Ú""," - ",LEFT(cells(i,2))"
         
11      End If
12  Next i
     
  13 Set myrange = Nothing
   
14  End Sub

lionliu

¦^´_ 7# GBKEE


   ÁÂÁÂgb¤j­ô¡A´ú¸Õ¤w¥i¥H¡C

GBKEE

¦^´_ 6# lionliu

1. Cells(i, 2) = "=VLOOKUP(" & Cells(i, 1).Address & ",ABC_Data!a:g,7,FALSE)"

½Æ»s¥N½X

¦ý§AªþÀɪº¤u§@ªíABC.[B4]¶}©lªºÀx¦s®æ®æ¦¡³£¬O¤å¦r,»Ý§ï¦¨³q¥Î®æ¦¡

lionliu

¦^´_ 5# GBKEE
ÁÂÁÂgbªº¸Ñµª¤w¸g¥i¥H¤F¡C
½Ð¥s¤j­ô¦³Ãö©óÃþ¦ü¦ì§}ªº¼gªk¡A¬O§_³£¥i¥Î  """ & cells(i ,j) & """   ³o¼Ë¨Ó³B²z©O
ÁÙ¬O³o¬O¨ú¸ÓÄæ¦ìªº­È¡A­Y­n¨ú¦ì¸m(A2)À³¦p¦ó³B²z

GBKEE

¦^´_ 4# lionliu

1. ¤u§@ªí¨ç¼ÆVLOOKUP
   
2. VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
   
3. Lookup_value    ¬O±z¥´ºâ¦b°}¦Cªº³Ì¥ªÄ椤·j´Mªº­È¡CLookup_value ¥i¥H¬O¼Æ­È¡B°Ñ·Ó¦ì§}©Î¤å¦r¦r¦ê¡C

½Æ»s¥N½X

Lookup_value: ·í·j´Mªº­È¬O¦r¦ê,¦ý§A¤£¬O«ü©w°Ñ·Ó¦ì§}®É
¤u§@ªí¤Wªº¨ç¼Æ¤½¦¡=VLOOKUP("PB04002",ABC_Data!A1:G50,7,FALSE)

1. Cells(i, 2) = "=VLOOKUP(""" & Cells(i, 1) & """,ABC_Data!a:g,7,FALSE)"
   
2.           'Cells(i, 2) = "=VLOOKUP(" & Cells(i, 1) & ",ABC_Data!a1:g50,7,FALSE)"
   
3.          

½Æ»s¥N½X

lionliu

GB¤j¤j
  ¸g¹L´ú¸Õ«áVLOOKUPªº³¡¤ÀÁÙ¬O¤£¦æ¡A¥u¥X²{¤½¦¡
¥i§_À°§Ú¬Ý¤@¤U¡C
test.rar (10.2 KB)

´ú¸ÕÀÉ
¤U¸ü¦¸¼Æ: 6

2014-5-2 14:06

lionliu

·PÁÂgbkee¤j­Óªº±Ð¾Ç¡G
      
     ÁÂÁÂGB¤j­ô¸Ñ´b³á¡A­ì¨ÓÁÙ¦³³o»ò¦hªº³Z¬¦b¸Ì­±¡C
§Ú·Q¦n¦h°ò¥»Æ[©À³£³Q§Úµ¹©¿²¤±¼¤F¡A¦b¤j­ô¸Ô²Ó¸Ñ»¡¤U¤SÀ°§Ú¶}¤F¤@®°µ¡¡C
§Ú­n»°§Ö¨Ó´ú¸Õ°Õ¡A¦³°ÝÃD¦b¦V¤j®a³ø§i¡C

:D :D :D

GBKEE

¦^´_ 1# lionliu

1. Option Explicit
   
2. Sub trnprg()
   
3.     Dim myrow As Long
   
4.     Dim myrange As Range
   
5.     Dim i As Integer
   
6.     myrow = Range("a1").End(xlDown).Row
   
7.     For i = 2 To myrow
   
8.         Set myrange = Cells(i, 1)
   
9.         If myrange <> Empty Then
   
10.           'Cells(i, 2).FormulaR1C1 = "=VLOOKUP(" & Cells(1, i) & ",Jsc_Data!A:G,6,FALSE)" '<-¬OR1C1,¦ý¦³A:G
    
11.          
    
12.           'FormulaR1C1 ¶Ç¦^©Î³]©wª«¥óªº¤½¦¡¡A¥Î¥¨¶°»y¨¥ªº R1C1 ¼Ë¦¡²Å¸¹ªí¥Ü
    
13.           'Cells(i, 2).FormulaR1C1 = "=VLOOKUP(" & Cells(1, i) & ",Jsc_Data!A:C1:C7,6,FALSE)"
    
14.          
    
15.           'Formula ¶Ç¦^©Î³]©w A1 ¼Ë¦¡ªºª«¥ó¤½¦¡
    
16.           'Cells(i, 2).Formula = "=VLOOKUP(" & Cells(1, i) & ",Jsc_Data!A:G,6,FALSE)"
    
17.           '
    
18.           Cells(i, 2) = "=VLOOKUP(" & Cells(1, i) & ",Jsc_Data!A:G,6,FALSE)"  '²³æ¤@ÂI
    
19.          
    
20.           '¦ý VLOOKUP ¶Ç¦^#N/A ®É Cells(i, 5).FormulaR1C1 ·|¿ù»~
    
21.             Cells(i, 5).FormulaR1C1 = "=CONCATENATE(""¥N¹Ô´Ú"", "" - "",Left(""" & Cells(i, 2) & """,2))"
    
22.             If Not IsError(Cells(i, 2)) Then Cells(i, 5) = "¥N¹Ô´Ú  - " & Left(Cells(i, 2), 2)
    
23.             '¦ó¤£§ï¬°¦p¦¹
    
24.         End If
    
25.     Next i
    
26.     Set myrange = Nothing
    
27.   End Sub

½Æ»s¥N½X