[µo°Ý] ¦¹¦¬´Mvba¼W¥[¸U¥Î¦r¤¸

die78325

¥H¤U¬O§Ú¼gªº¦¬´M¨Ã¦Cªíµ{¦¡
¦ý§Æ±æ¯à°÷§ïÅܦ¨  M3¿é¤J"±i"   ´N¦C¥X¦³"±i"³o­Óªº¦rªº
¥Ø«e¬O¿é¤J§¹¾ã¥þ¦W¤~·|¦Cªí....§Æ±æ¦U¦ì¤j¤jÀ°¦£!^^ ·P®¦¤£ºÉ!   


Sub ²Î¤@¬d¸ß¥\¯à()
'Ãö³¬Åã¥Ü
Application.ScreenUpdating = False

Set Name = Sheets("¬d¸ß³B²z°Ï").Range("M3")
Sheets("²Î¤@").Select
G = [A65536].End(xlUp).Row
For i = 2 To G
    '¦pªG¦³¤@¼Ë ´N¦V¥ª¨Ã½Æ»s Âà­È¶K¤W¦Ü¬d¸ß°Ï
    If Sheets("²Î¤@").Cells(i, 9) = Name Then
    Sheets("²Î¤@").Select
    Cells(i, 9).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Selection.Copy
    Sheets("¬d¸ß³B²z°Ï").Select
    c = [A65536].End(xlUp).Row
    Cells(c + 1, 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End If
Next i
Sheets("¬d¸ß³B²z°Ï").Select
Application.ScreenUpdating = True  '¶}±ÒÅã¥Ü
MsgBox "²Î¤@¬d¸ß§¹¦¨"
End Sub

Hsieh

¦^´_ 1# die78325


    If InStr(Sheets("²Î¤@").Cells(i, 9), Name) > 0 Then

die78325

¦^´_ 2# Hsieh


¤j¤j  §ï³o±ø ¤£¬O§Ú­nªº....
§Ú¥Ø«e¥»¨Ó´N¥i¥H¦¬´M¥þ¦W   ¥u¬O ¤£¯à§Q¥Î¨ä¤¤¤@­Ó¦r¨Ó¥h¦¬´M..... ¥´±i ´N­nÅã¥Ü¦³Ãö±iªº³£­n¥X¨Ó  
ÁÙ¬O·PÁ¤j¤j¼ö¤ß¦^À³§Ú   ½Ð¦AÀ°§Ú·Q·Q¿ìªk~~~·P®¦¤£ºÉ!!

Hsieh

¦^´_ 3# die78325


    Instr¨ç¼Æ´N¬O¶Ç¦^¬Y¦r¤¸¦ì©ó¦r¦ê¤¤ªº¦ì¸m(»P¤u§@ªí¨ç¼ÆFIND¬Û¦P)
­Y¦r¦ê¤¤¤£¥]§t·j´M¦r¤¸«h¶Ç¦^0
»P¤U¦CLIKE¹Bºâ±o¨ì¬Û¦P®ÄªG
If Sheets("²Î¤@").Cells(i, 9) Like "*" & Name & "*" Then

die78325

¦^´_ 4# Hsieh


    ¤j¤j  ¨º¦pªG§Ú·Q­n¥Î³oµ{¦¡  µM«á¥L¶]ªº¹Lµ{¤¤ ¦A¥[¤@­Ó°»´ú  ¦pªG¤w¸g¦³¬Û¦Pªº ´N¤£¶K¤F
¸Ó«ç»ò­×§ï©O  ·PÁ¤j¤j.....¬ã¨s¦n¤[¤F....

kimbal

¦^´_ 5# die78325


    §Aªº·N«ä¬O¦h©ó¤@­Ó±ø¥ó?
    ¦p  name="±i", name2="©ú"
    §ä¥X¨Óªº¥i¥H¬O "±i¤p©ú" , "±i¤j©ú", "©ú¤p±i" ©M "©ú¤j±i"

1. If (Sheets("²Î¤@").Cells(i, 9) Like "*" & Name & "*") and (Sheets("²Î¤@").Cells(i, 9) Like "*" & Name2 & "*")  Then
   
2.       ...
   
3.   end if

½Æ»s¥N½X

¦pªG¦³¥ý«á¦¸§Çªº¸Ü,     (§Y§ä¥X¨Óªº¥i¥H¬O "±i¤p©ú" ©M "±i¤j©ú",¨S¦³ "©ú¤p±i" ©M "©ú¤j±i")

1. If (Sheets("²Î¤@").Cells(i, 9) Like "*" & Name &"*" & Name2 & "*") Then
   
2.       ...
   
3. end if

½Æ»s¥N½X

©ÎªÌ¦A²³æ¤@ÂI¥u­n§âNAME³]©w¬° "±i*©ú", ´N¥i¥H±o¥X¤W­±²Ä2³¡¥÷ªº®ÄªG

Hsieh

¦^´_ 5# die78325

1. Sub ²Î¤@¬d¸ß¥\¯à()
   
2. 'Ãö³¬Åã¥Ü
   
3. Application.ScreenUpdating = False
   
4. Set d = CreateObject("Scripting.Dictionary")
   
5. Set Name = Sheets("¬d¸ß³B²z°Ï").Range("M3")
   
6. With Sheets("²Î¤@")
   
7. G = .[A65536].End(xlUp).Row
   
8. For i = 2 To G
   
9.     If .Cells(i, 9) Like "*" & Name & "*" And IsEmpty(d(.Cells(i, 9).Value)) Then
   
10.     Sheets(1).[A65536].End(xlUp).Offset(1).Resize(, 9) = Cells(i, 1).Resize(, 9).Value
    
11.     d(.Cells(i, 9).Value) = .Cells(i, 9).Value
    
12.     End If
    
13. Next i
    
14. Sheets("¬d¸ß³B²z°Ï").Select
    
15. Application.ScreenUpdating = True  '¶}±ÒÅã¥Ü
    
16. MsgBox "²Î¤@¬d¸ß§¹¦¨"
    
17. End Sub

½Æ»s¥N½X

die78325

¦^´_ 7# Hsieh


   
¨S¦³ end with
¤j¤j¥i¥H¶¶«K¸ÑÄÀ¤@¤U¶Ü ¦³¨Ç¥N½X¤£¤Ó¤F¸Ñ¬Æ»ò·N«ä....·PÁÂ!

die78325

¦^´_ 6# kimbal


    ÁÂÁ¤j¤j¤S±Ð§Ú¤F¤@©Û^^~~ ·PÁ±z!!

¦ý§Ú­nªº·N«ä¬O .....°²³]A¤u§@­¶¦³100µ§¸ê®Æ  ¦³20µ§ ¦W¤l¥]¬A±iªº(A­¶¬O·|³°Äò·s¼W¸ê®Æ) ¹L´X¤Ñ¥i¯àÅÜ25µ§   
B­¶¬O©T©w¦¬´M­¶(¦¬´M"±i"ªº)  °²³]B­¶¬O¤w¸g¦³ 15µ§"±i"ªº   §Ú«ö¤U¦¬´M«á   ¥L·|¨Ì´`A­¶¥h´M§ä "±i"ªº  ¦ý§ä¨ì±i­n¶K¹L¨Ó®É  ­n¥h¬ÝB­¶¦³¨S¦³³o¤@µ§¤F ¦³ªº¸Ü ´N¤£¶K Ä~Äò©¹¤U§ä ¨S¦³ ´N¶K¤JB­¶³Ì¤U¦C

Hsieh

1. Sub ²Î¤@¬d¸ß¥\¯à()
   
2. 
   
3. 'Ãö³¬Åã¥Ü
   
4. 
   
5. Application.ScreenUpdating = False
   
6. 
   
7. Set d = CreateObject("Scripting.Dictionary")  '³Ð«Ø¦r¨åª«¥ó
   
8. 
   
9. Name = Sheets("¬d¸ß³B²z°Ï").Range("M3")  '¬d¸ßÃöÁä¦r
   
10. 
    
11. With Sheets("²Î¤@")  '²Î¤@¤u§@ªí°Ï¬q
    
12. 
    
13. G = .[A65536].End(xlUp).Row   '²Î¤@¤u§@ªíªº¸ê®Æ§À
    
14. 
    
15. For i = 2 To G
    
16. 
    
17.     If .Cells(i, 9) Like "*" & Name & "*" And IsEmpty(d(.Cells(i, 9).Value)) Then '¦r¨å¤¤¨S¥X²{¹LIÄæ¥B¥]§t¬d¸ßÃöÁä¦r
    
18. 
    
19.     Sheets("¬d¸ß³B²z°Ï").[A65536].End(xlUp).Offset(1).Resize(, 9) = .Cells(i, 1).Resize(, 9).Value  '±NA:IÄæ¼g¤JSheets("¬d¸ß³B²z°Ï")
    
20. 
    
21.     d(.Cells(i, 9).Value) = .Cells(i, 9).Value  '±NIÄ椺®e¦s¤J¦r¨å
    
22. 
    
23.     End If
    
24. 
    
25. Next i
    
26. End With 'µ²§ô²Î¤@¤u§@ªí°Ï¬q±Ô­z
    
27. Sheets("¬d¸ß³B²z°Ï").Select
    
28. 
    
29. Application.ScreenUpdating = True  '¶}±ÒÅã¥Ü
    
30. 
    
31. MsgBox "²Î¤@¬d¸ß§¹¦¨"
    
32. 
    
33. End Sub

½Æ»s¥N½X

¦^´_ 9# die78325