GBKEE

¦^´_ 1# cslinmiso
¨Ï¥ÎDictionaryª«¥ó

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim d As Object, Rng As Range
   
4.     Set d = CreateObject("SCRIPTING.DICTIONARY")  '³]¥ß Dictionaryª«¥ó
   
5.     Set Rng = Sheets("sheet1").[a2]
   
6.     Do
   
7.         d(Format(Rng, "yyyy/m/d") & Format(Rng.Offset(, 1), "hh:mm") & Rng.Offset(, 2)) = Rng.Offset(, 3).Resize(, 3)
   
8.         '2012/9/2200:30Aaron        -> Dictionaryª«¥óªº key: Format(Rng, "yyyy/m/d") & Format(Rng.Offset(, 1), "hh:mm") & Rng.Offset(, 2)
   
9.         'Rng.Offset(, 3).Resize(, 3)-> Dictionaryª«¥óªº item (D:E:FÄæ)
   
10.         Set Rng = Rng.Offset(1)                   'Åܼƪ«¥ó ¤U²¾¤@¦C
    
11.     Loop Until Rng.Value = ""                     'Åܼƪ«¥ó¤º®e=ªÅ¥Õ¦r¦êÂ÷¶}°j°é
    
12.     Set Rng = Sheets("sheet2").[a2]
    
13.     Do
    
14.         If d.Exists(Rng.Text & Rng.Offset(, 1).Text & Rng.Offset(, 2)) Then
    
15.             'Exists ¤èªk ¦pªG¦b Dictionary ª«¥ó¤¤«ü©wªºÃöÁä¦r¦s¦b¡A¶Ç¦^ True¡A­Y¤£¦s¦b¡A¶Ç¦^ False¡C
    
16.             Rng.Offset(, 3).Resize(, 3).Value = d(Rng & Rng.Offset(, 1).Text & Rng.Offset(, 2))
    
17.              'D:FÄæ.Value=Dictionaryª«¥óªºitem
    
18.         End If
    
19.         Set Rng = Rng.Offset(1)
    
20.     Loop Until Rng = ""
    
21. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 7# cslinmiso
"¬O§_±N    Set Rng = Sheets("sheet1").[a2] ¦¹¦æ  ©w¸q¦¨worksheet.sheet("ÀɦW").[a2] §Y¥i©O?"
§A¸Õ¸Õ´Nª¾¹D

GBKEE

¦^´_ 11# cslinmiso
ªþÀÉ ¥i¨Ì 5# ¤Î 6#  Hsieh ¶Wª© ªºµ{¦¡ ±o¨ì
¥u¬O  6#  Hsieh ¶Wª© ªºµ{¦¡ ­×§ï  With  ¤u§@ªí1  -> With Sheets("sheet1") ' ¤u§@ªí1
                                                            With  ¤u§@ªí2  -> With Sheets("sheet2") ' ¤u§@ªí2
¥t­×§ï 11# ªºµ{¦¡½X ¸Õ¸Õ¬Ý ¬O§_¦X¥Î?

1. Option Explicit
   
2. Sub Macro8()
   
3. Dim mySubtotal As Double
   
4. Dim mytype As Variant
   
5. Dim Wkabc As Variant
   
6. Dim Wkvip As Variant
   
7. Dim myx, myi As Integer
   
8. Dim i As Integer
   
9. Dim searchTerm As String
   
10. Dim myArray(10) As String
    
11. 
    
12. Dim Filename, Wo As Workbook, Filename_Msg As Boolean
    
13. Application.ScreenUpdating = False 'Ãö³¬¿Ã¹õ§ó·s
    
14. Wkabc = Application.InputBox(prompt:="Please Input Current Week", Title:="Weekly Report Letter for ABC")
    
15. Filename = "Week " & Wkabc & "¤ÀªR" & ".XLS" 'report¦WºÙ§ó§ï¶g¼Æ
    
16. '·j´M¶}±Òªºreport¦WºÙ
    
17.     For Each Wo In Workbooks
    
18.          If Wo.Name = Filename Then
    
19.             Filename_Msg = True
    
20.             Exit For
    
21.          End If
    
22.     Next
    
23.     If Filename_Msg = False Then
    
24.         MsgBox "§ä¤£¨ì¦¹³ø§i¡A½T»{¬O§_¿é¤J¥¿½T¨Ã½T»{Àɮפw¸g¶}±Ò"
    
25.         Exit Sub
    
26.     End If
    
27.      
    
28. Filter1:
    
29.     mytype = Array("Absent", "Client", "Computer", "Connection", "Consultant Shortage", "Disconnection", "Disconnection(Idle)", "Headset", "Instant Break (Disconnection)", "Other", "Power Outage", "System", "TE")
    
30.     For myi = 0 To 12 '­p¼Æmyi 13 ¦¸
    
31. ' 'Filter
    
32.         Workbooks(Filename).Activate
    
33.         'mySubtotal = D10      '¿ù»~ ¨S¦³³]¥ßÅܼÆ
    
34.         Worksheets("¤ÀªR").Range("$A$1:$J$250").AutoFilter Field:=9, Criteria1:=mytype(myi)
    
35.             'Count range
    
36.         mySubtotal = Application.WorksheetFunction.Subtotal(3, Worksheets("¤ÀªR").Range("B2:B250"))
    
37.         Windows("Weekly Refund report letter.xls").Activate
    
38.         Range("D" & myi + 10) = mySubtotal
    
39.     Next myi
    
40.     Workbooks(Filename).Activate
    
41.     ActiveSheet.Range("$A$1:$J$250").AutoFilter Field:=9 '¶}¦^¿z¿ïALL
    
42. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 13# cslinmiso
"±N°}¦C§ï¦¨for each¡A½Ð°Ý¦¹ºØ¼gªk¬O§_¤ñ¸ûª½Ä±©ÎªÌ¤ñ¸û²³æ©O¡H"
³oºÝ¬Ý­Ó¤Hªº²ßºD¦Ó©w