ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

¥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

¦^´_ 1# die78325


    If InStr(Sheets("²Î¤@").Cells(i, 9), Name) > 0 Then
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 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®¦¤£ºÉ!!

TOP

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 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....

TOP

¥»©«³Ì«á¥Ñ kimbal ©ó 2012-11-19 22:28 ½s¿è

¦^´_ 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
À´±oµo°Ý,µª®×´N·|¦b¨ä¤¤

¤µ¤éの¤@¬íは  ©ú¤éにない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 7# Hsieh


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

TOP

¦^´_ 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

TOP

  1. Sub ²Î¤@¬d¸ß¥\¯à()

  2. 'Ãö³¬Åã¥Ü

  3. Application.ScreenUpdating = False

  4. Set d = CreateObject("Scripting.Dictionary")  '³Ð«Ø¦r¨åª«¥ó

  5. Name = Sheets("¬d¸ß³B²z°Ï").Range("M3")  '¬d¸ßÃöÁä¦r

  6. With Sheets("²Î¤@")  '²Î¤@¤u§@ªí°Ï¬q

  7. G = .[A65536].End(xlUp).Row   '²Î¤@¤u§@ªíªº¸ê®Æ§À

  8. For i = 2 To G

  9.     If .Cells(i, 9) Like "*" & Name & "*" And IsEmpty(d(.Cells(i, 9).Value)) Then '¦r¨å¤¤¨S¥X²{¹LIÄæ¥B¥]§t¬d¸ßÃöÁä¦r

  10.     Sheets("¬d¸ß³B²z°Ï").[A65536].End(xlUp).Offset(1).Resize(, 9) = .Cells(i, 1).Resize(, 9).Value  '±NA:IÄæ¼g¤JSheets("¬d¸ß³B²z°Ï")

  11.     d(.Cells(i, 9).Value) = .Cells(i, 9).Value  '±NIÄ椺®e¦s¤J¦r¨å

  12.     End If

  13. Next i
  14. End With 'µ²§ô²Î¤@¤u§@ªí°Ï¬q±Ô­z
  15. Sheets("¬d¸ß³B²z°Ï").Select

  16. Application.ScreenUpdating = True  '¶}±ÒÅã¥Ü

  17. MsgBox "²Î¤@¬d¸ß§¹¦¨"

  18. End Sub
½Æ»s¥N½X
¦^´_ 9# die78325
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¨Ã«D¦³¿ú¾{¬O§Ö¼Ö¡A°Ý¤ßµL·\¤ß³Ì¦w¡C
ªð¦^¦Cªí ¤W¤@¥DÃD