Hsieh

¦^´_ 1# die78325


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

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

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

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

Hsieh

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

½Æ»s¥N½X

¦^´_ 12# die78325

Hsieh

¦^´_ 13# die78325
µ{¦¡½X¬O¤ñ¹ïA»PIÄ椣­«½Æ
­Y¬O­n¥HAÄæ®É¶¡¨ì¤ÀÄÁ¬°§PÂ_°ò·Ç

1. Sub ²Î¤@¬d¸ß¥\¯à()
   
2. Application.ScreenUpdating = False
   
3. Set d = CreateObject("Scripting.Dictionary")  '³Ð«Ø¦r¨åª«¥ó
   
4. Name = Sheets("¬d¸ß³B²z°Ï").Range("M3")  '¬d¸ßÃöÁä¦r
   
5. With Sheets("²Î¤@")  '²Î¤@¤u§@ªí°Ï¬q
   
6. G = .[A65536].End(xlUp).Row   '²Î¤@¤u§@ªíªº¸ê®Æ§À
   
7. For i = 2 To G
   
8. mystr = Format(.Cells(i, 1), "yyyymmdd hhmm") & .Cells(i, 9) '¤£­«½Æ·Ç«h¦r¦ê
   
9.     If .Cells(i, 9) Like "*" & Name & "*" And IsEmpty(d(mystr)) 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(mystr) = .Cells(i, 9).Value  '±NIÄæ®ÚAÄ椺®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

Hsieh

¦^´_ 17# die78325

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

½Æ»s¥N½X

Hsieh

¦^´_ 22# die78325

Dim sName As Worksheet
For Each sName In Sheets(Array("²Î¤@", "¨ä¥L¤u§@ªí"))