EXCEL VBS

kyhuang

¦U¦ì«e½ú¦n:

¸Ó°ÝÃD­W«ä¤w¤[,·q½ÐÀ°¦£,Á°Õ!!

¥HC1ÀɦW(¤£¥]¬A°ÆÀɦW)´M§äA1,¦p¦³¬Û²Å¥HB1¨ú¥N¨ÃÅã¥Ü©óD1
¦p§ä¤£¨ì¼ÐµùX.¸Ô¦pªþ¥ó¼Ë¥».

¥ýÁÂ°Õ !!

¤U¸ü (64.33 KB)
½d¨Ò

2013-2-26 22:06

kyhuang

¦^´_ 2# lukychien


    ·PÁÂ¥S¥x¦¨¥\¤F,¶È¤Ö³¡¤À¥i¯à¦h¤@½XÂण¹L¨Ó

008ACH009  2120  008ACH009-1.JPG  X
¥¿½Tµ²ªGÀ³¬°
008ACH009  2120  008ACH009-1.JPG  2120-1.JPG

Hsieh

¦^´_ 1# kyhuang

1. Sub ex()
   
2. Set d = CreateObject("Scripting.Dictionary")
   
3. For Each a In Range([A1], [A1].End(xlDown))
   
4.    d(a.Value) = a.Offset(, 1)
   
5. Next
   
6. For Each a In Range([C1], [C1].End(xlDown))
   
7.    If d(Left(a, 8)) = "" Then
   
8.    a.Offset(, 1) = "X"
   
9.    Else
   
10.    a.Offset(, 1) = Replace(a, Left(a, 8), d(Left(a, 8)))
    
11.    End If
    
12. Next
    
13. End Sub

½Æ»s¥N½X

¦pªG¨Ï¥Î¨ç¼Æ
D1¤½¦¡
=IF(COUNTIF(A:A,LEFT(C1,8))=0,"X",SUBSTITUTE(C1,LEFT(C1,8),VLOOKUP(LEFT(C1,8),$A$1:$B$11,2,0)))
¦V¤U½Æ»s

lukychien

¦^´_ 3# kyhuang


°ÝÃDÀ³¸Ó¬O¥X¦bOffice 2003¬Ý¤£À´³¡¥÷2007¥Îªº¤½¦¡§a!!  (¦p, IFERROR)

½Ð¦A¸Õ¤@¤U¡A¨Ã§iª¾¬O§_¥i¦æ

D1=IF(ISERROR(VLOOKUP(LEFT(C1,8),A:B,2,0)),"X",CONCATENATE(VLOOKUP(LEFT(C1,8),A:B,2,0),IF(ISNUMBER(FIND("-",C1)),IF(FIND("-",C1),MID(C1,FIND("-",C1),2),""),""),".jpg"))

kyhuang

¦^´_ 2# lukychien


    ·PÁÂ,¦n¹³¤£¦æ.¬O±N¸Ó¦æ¸ê®Æ¶K¨ìD1¶Ü¡H¥X²{"¡­NAME¡H"

lukychien

¦^´_ 1# kyhuang

½Ð¸Õ¬Ý¬Ý³o¼Ë¦æ¶Ü
ÁöµMµ²ªG¬O¹ïªº¡A¦ýÁ`ı±o¦n¹³¨º¸Ì©Ç©Çªº?

D1=IFERROR(CONCATENATE(VLOOKUP(LEFT(C1,8),A:B,2,0),IF(ISNUMBER(FIND("-",C1)),IF(FIND("-",C1),MID(C1,FIND("-",C1),2),""),""),".jpg"),"X")