lifedidi

¦^´_ 4# Hsieh


¤j¤j¦n¡G

§ï¥Î¤j¤jªº»yªk«á¡Arun°_¨Ó¦³§Ö«Ü¦h¡A¦ýÁÙ¬O¦³­Ó¦a¤è¦³°ÝÃD
½Ð°Ý¤j¤j¤@­Ó°ÝÃD¡A
§Ú¤@ª½¥æ¤e´ú¸Õµo²{¨ì¡A
¿z¿ï¨Ã¶K¤Wªº»yªk¡A«Ü¶O®É¶¡¡A
µ{¦¡½X¦p¤U¡G

1. Dim HH As Range, KK As Range, PP As Range
   
2. Set PP = .[A6]
   
3.        .[A1:W65536].ClearContents
   
4. HH.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=KK,

½Æ»s¥N½X

¡i°²³]¤­¤dµ§¸ê®Æ¡j
run°_¨Ó¡A¸ê®Æ¤@µ§¤@µ§¶K¤W¡A¶K¨ì¤­¤dµ§»Ý­n¤£¤Ö®É¶¡¡A
(§Ú±NÀx¦s®æ©Ô¦Ü5000¦C³B¡A«ö°õ¦æ¡Aµo²{­n¤@¬q®É¶¡¤~·|run¨ì¤­¤dµ§¡A¦]¦¹§PÂ_¬O³o°ÝÃD)
¥i¬O§Ú§â¤@¼Ëªº¸ê®Æ­ì«Ê¤£°Êªº¶K¨ì·ssheetÀÉ(new excel file)¡A¦brun¡A¤£¨ì¤@¬í´N¶]§¹¤F¡A(........)

(PS)¾ã­ÓexcelÀɮצ³¬ù20­Óuserform¨C­Ó³£¿z¿ï¤£¦P±ø¥ó¡A¤p§Ì¦b²q·Q¡A³o¼Ë·|¤£·|³y¦¨excel°õ¦æ®Éªº½wºC¡H
½Ð¯q¤j¤j·N¨£¡A¤p§Ì²{¦bÀY¦n·w=.= ·PÁÂ

Hsieh

¦^´_ 3# lifedidi
¥Î§A­ì¨Óªº¶i¶¥¿z¿ï
²Î­p³¡¤À§ï¥Î¨ç¼Æ­pºâ§Y¥i

1. Private Sub CommandButton1_Click()  '¬d¸ß¶s
   
2.     Dim d1 As Date, d2 As Date
   
3.     Dim Srng As Range, Crng As Range, Orng As Range
   
4.     Set Srng = Sheets("SHEET1").[A6].CurrentRegion
   
5.     Set Crng = Sheets("¿z¿ï¥Î").[A1:A2]
   
6.     With Sheets("SHEET2")
   
7.         Set Orng = .[A6]
   
8.         .[A1:W65536].ClearContents
   
9.         Srng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Crng, CopyToRange:=Orng
   
10.         If .[A7].Value = "" Then
    
11.             MsgBox "¨S¸ê®Æ", vbCritical + vbOKOnly, "ª`·N"
    
12.             Exit Sub
    
13.         End If
    
14.     TextBox1.Value = Application.Text(Application.Sum(.[R:R]), "[hh]:mm")
    
15.     End With
    
16. End Sub

½Æ»s¥N½X

lifedidi

¦^´_ 2# GBKEE


    GBKEE¤j¤j¦n¡G

´ú¸Õ¹LOK¡A¦ý¬O¨S¦³±N¿z¿ïªº¸ê®Æ¶K©óSHEET2¡A¬O§_½Ð¤j¤j¦b½s¤@¤U¡A

¤p§Ì¬ã¨s¤@¤U¤j¤jªºµù¸Ñ;P ·PÁ¡C

GBKEE

¦^´_ 1# lifedidi
¸Õ¸Õ¬Ý

1. Private Sub UserForm_Initialize()   '°ò¥»³]©w
   
2.   '  Set «¬¸¹ = CreateObject("Scripting.Dictionary")  '¯Ó®É:¶·¶]§¹©Ò¦³¸ê®Æ¦C
   
3.     Dim X As Integer
   
4.    ' Sheets("¿z¿ï¥Î").[A2:A2].ClearContents
   
5.     With Sheets("SHEET1")
   
6.         'er = .[A65536].End(3).Row
   
7.         'myrng = .Range("A7:R" & er)        '¸ê®Æ¼Æ¨Ó¨ì10000µ§¥H¤W®É ***³o·|¦û¥Î°O¾ÐÅé***
   
8.          .Cells(1, .Columns.Count) = ""
   
9.         .Range("D6", .[D6].End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, .Columns.Count), Unique:=True
   
10.         '¶i¶¥¿z¿ï DÄæ ¤£­«½Æ¸ê®Æ Unique:=True ¨ì³Ì«á¤@Äæ
    
11.         X = 2
    
12.         Do While .Cells(X, .Columns.Count) <> ""
    
13.             ComboBox1.AddItem .Cells(X, .Columns.Count)
    
14.             X = X + 1
    
15.         Loop
    
16.         .Columns(.Columns.Count).EntireColumn.Clear   '²M°£ ³Ì«á¤@Äæ¸ê®Æ
    
17.     End With
    
18. End Sub
    
19. Private Sub ComboBox1_Change()  '¿ï¾Ü ¤U©Ô¦¡¿ï³æ1 ¥ß§YÅã¥ÜÁ`Á`®É¶¡;¥i¤£¥Î¬d¸ß¶s
    
20.     If ComboBox1.ListIndex = -1 Then   '¤£¦b¤U©Ô¦¡¿ï³æªº²M³æ¤º
    
21.         MsgBox "±M®×½s¸¹ ½s¸¹ " & ComboBox1 & " ¤£¥¿½T"
    
22.     Else
    
23.         Application.ScreenUpdating = False
    
24.         With Sheets("SHEET1")
    
25.             .Range("a6").AutoFilter Field:=4, Criteria1:=ComboBox1            'AutoFilter:  ­ì¸ê®Æ®w¤W¦Û°Ê¿z¿ï.
    
26.             With .Range("r:r").SpecialCells(xlCellTypeVisible)
    
27.                 TextBox1.Value = Application.Text(Application.Sum(.Cells), "[hh]:mm")
    
28.             End With
    
29.             .AutoFilterMode = False
    
30.         End With
    
31.         Application.ScreenUpdating = True
    
32.     End If
    
33. End Sub
    
34. Private Sub CommandButton3_Click()  'Â÷¶}¶s
    
35.     Unload UserForm1
    
36. End Sub
    
37. Private Sub UserForm_Terminate()    '­«·s±Æ¦C
    
38.     'Sheets("SHEET1").Range("A7:R" & er) = myrng   '­«·s¶ñ¤W¸ê®Æ¯Ó®É
    
39. End Sub

½Æ»s¥N½X