Hsieh

¦^´_ 1# lifedidi
ªí³æ¼Ò²Õ

1. Private Sub UserForm_Initialize()
   
2. Set d = CreateObject("Scripting.Dictionary")
   
3. With Sheet1
   
4.    For Each a In .Range(.[A1], .[A1].End(xlDown))
   
5.    d(a.Value) = ""
   
6.    Next
   
7. End With
   
8. ComboBox1.List = d.keys
   
9. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 3# lifedidi

Sheet2¼Ò²Õ

1. Private Sub CommandButton1_Click()
   
2.    FormA.Show 0
   
3. End Sub

½Æ»s¥N½X

FormA¼Ò²Õ

1. Private Sub CommandButton1_Click()
   
2. FormB.Show 0
   
3. End Sub
   
4. 
   
5. Private Sub UserForm_Initialize()
   
6. Set d = CreateObject("Scripting.Dictionary")
   
7. With Sheet1
   
8.    For Each a In .Range(.[A2], .[A1].End(xlDown))
   
9.    d(a.Value) = ""
   
10.    Next
    
11. End With
    
12. ComboBox1.List = d.keys
    
13. End Sub

½Æ»s¥N½X

FormB¼Ò²Õ

1. Private Sub CommandButton1_Click()
   
2. FormC.Show 0
   
3. FormC.­pºâ 1
   
4. End Sub
   
5. 
   
6. Private Sub CommandButton2_Click()
   
7. FormC.Show 0
   
8. FormC.­pºâ 2
   
9. End Sub

½Æ»s¥N½X

FormC¼Ò²Õ

1. Sub ­pºâ(work As Integer)
   
2. Dim Ar()
   
3. With Sheet1
   
4.    For Each a In .Range(.[A2], .[A1].End(xlDown))
   
5.       If a.Value = FormA.ComboBox1.Value Then
   
6.          ReDim Preserve Ar(s)
   
7.          Ar(s) = a.Offset(, 1).Value
   
8.          s = s + 1
   
9.       End If
   
10.    Next
    
11. End With
    
12. If work = 1 Then ListBox1.AddItem Format(Application.Sum(Ar), "hh:mm:ss")
    
13. If work = 2 Then ListBox1.AddItem Format(Application.Average(Ar), "hh:mm:ss")
    
14. End Sub

½Æ»s¥N½X

Form Test.zip (12.55 KB)

¤U¸ü¦¸¼Æ: 23

2013-2-26 14:18

Hsieh

¦^´_ 5# lifedidi
§A¬O­n¦bLISTBOX¤ºÅã¥Ü©Î¬OÀx¦s®æ¤ºÅã¥Ü?
LISTBOX¤º­nÅã¥Ü¶W¹L24¤p®É¥[Á`®É¶¡

1. Sub ­pºâ(work As Integer)
   
2. Dim Ar()
   
3. With Sheet1
   
4.    For Each a In .Range(.[A2], .[A1].End(xlDown))
   
5.       If a.Value = FormA.ComboBox1.Value Then
   
6.          ReDim Preserve Ar(s)
   
7.          Ar(s) = a.Offset(, 1).Value
   
8.          s = s + 1
   
9.       End If
   
10.    Next
    
11. End With
    
12. If work = 1 Then ListBox1.AddItem Application.Text(Application.Sum(Ar), "[hh]:mm:ss")
    
13. If work = 2 Then ListBox1.AddItem Application.Text(Application.Average(Ar), "[hh]:mm:ss")
    
14. End Sub

½Æ»s¥N½X

­YÀx¦s®æ®æ¦¡«h¦Û­q¬°[hh]:mm

Hsieh

¦^´_ 7# lifedidi
­º¥ý¥ý§â©Ò¦³Form.Showªº°Ñ¼Æ¥[¤W0
Form.Show 0
Åý¶}±Òªºªí³æ³£¬°«D±j¨î¦^À³

1. Sub ­pºâ(work As Integer)
   
2. Dim Ar(), Ay()
   
3. With Sheet1
   
4.    For Each a In .Range(.[D7], .[D7].End(xlDown))  '¦bDÄ檺¸ê®Æ´`Àô
   
5.       If a.Value = ±M®×½s¸¹.ComboBox1.Value Then   '¦pªGDÄ檺­Èµ¥©ó¤U©Ô¿ï³æªº­È
   
6.          ReDim Preserve Ar(s)  '«O¯d°}¦C¤¸¯À¨Ã­«³]°}¦C¤W­­
   
7.          ReDim Preserve Ay(s)
   
8.          Ar(s) = a.Offset(, 17).Value  '±NDÄæ¦V¥k17Ä檺­È¼g¤J°}¦C
   
9.          Ay(s) = a.Offset(, -3).Resize(, 26).Value  '±NA:ZÄ檺­È¼g¤J°}¦C
   
10.          s = s + 1  '¹w³Æ¤U¦¸°}¦CÂX®iªº¤W­­
    
11.       End If
    
12.    Next
    
13. End With
    
14. If s > 0 Then  '¦pªG¦³²Å¦Xªº¸ê®Æ
    
15. Sheet2.Range("C5").CurrentRegion.Offset(2) = ""   '¥ý²MªÅ¤W¦¸ªº¬d¸ß¤º®e
    
16. Sheet2.[C7].Resize(s, 26) = Application.Transpose(Application.Transpose(Ay))   '¼g¤J¤u§@ªí
    
17. If work = 1 Then ListBox1.AddItem Application.Text(Application.Sum(Ar), "[hh]:mm:ss")  '¤å¦r¤è¶ôÅã¥Ü¥[Á`µ²ªG
    
18. If work = 2 Then ListBox1.AddItem Application.Text(Application.Average(Ar), "[hh]:mm:ss")  '¤å¦r¤è¶ôÅã¥Ü¥­§¡µ²ªG
    
19. End If
    
20. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 10# lifedidi
¤£¬O«Ü²M·¡§A­n¬Æ»ò¡A¸Õ¸Õ¬Ýªþ¥ó¬yµ{¬Ý¬O§_²Å¦X»Ý¨D

¤u®É¨t²Îexcelª©¥»).rar (51.31 KB)

¤U¸ü¦¸¼Æ: 13

2013-2-27 23:24