cslinmiso

¦^´_ 10# GBKEE


·PÁª©¥D¦A¦¸¦^ÂСA¤p§Ì·|¸ÕÅç¬Ý¬Ý¡A¹J¿ù¦Aµo°ÝÁٽЫü±Ð¡C

¦¹¥~¡A¤p§Ìµo²{¤§«eµ¹ªºÀɮ׮榡¦³¿ù»~¡A¸Õ¥Î¥¿½T®æ¦¡ªºÀɮסA¦U¦ì¤j­ôªºµ{¦¡³£¶]¤£¥X¨Ó¤F¡C
Äæ¦ì¤@¤Á³£¤@¼Ë¡A¦ý¬O´N¬O¶]¤£¥X¨Ó¡A§Ú­«°µ¤F¤@¦¸ªí®æ¤º®e¤]¦³§ó°Ê¡A·Q½Ð¦U¦ì¦A¦¸«üÂI¡AÁÂÁ¡C

Compa.rar (11.55 KB)

¤U¸ü¦¸¼Æ: 7

2012-10-2 10:00

-------
¥t¥~ÂǦ¹¾÷·|¶¶«K°Ý¤@¤U¤p§Ì¼gªº¤@­Óµ{¦¡¬O§_¥i¥H§ó²¼ä¡A©ÎªÌ¦³§ó¦nªº¼gªk¡C
ÁÂÁÂ

¬Û«H¦U¦ì³£À³¸Ó¬Ý±oÀ´¡A¤£¹L§ÚÁÙ¬O¤j·§¸ÑÄÀ¤@¤U
¦¹µ{¦¡¬O¨ú¥ÎWeek  (¶g¼Æ)¤ÀªR.XLS ¸Ó³øªí¤ºªº¼Æ¾Ú
¥ý¥H¬YÄæ(mytype)¿z¿ï¥X¦U¶µ«á­p¼Æ¶ñ¤J ¥t¥~¤@¥÷³øªíWeekly Refund report letter.xls ¤§ D10Äæ¦ì¶}©l13­ÓÄæ¦ì
¤j·§§Y¬O¦p¦¹

1. Sub Macro8()
   
2. Dim mySubtotal As Double
   
3. Dim mytype As Variant
   
4. Dim Wkabc As Variant
   
5. Dim Wkvip As Variant
   
6. Dim myx, myi As Integer
   
7. Dim i As Integer
   
8. Dim searchTerm As String
   
9. Dim myArray(10) As String
   
10. 
    
11. Application.ScreenUpdating = False 'Ãö³¬¿Ã¹õ§ó·s
    
12. 
    
13. Wkabc = Application.InputBox(prompt:="Please Input Current Week", Title:="Weekly Report Letter for ABC")
    
14.    Filename = "Week " & Wkabc & "¤ÀªR" & ".XLS" 'report¦WºÙ§ó§ï¶g¼Æ
    
15. '·j´M¶}±Òªºreport¦WºÙ
    
16.     For i = 1 To Workbooks.Count
    
17.        'Arr = Array(Workbooks(i).Name)
    
18.          myArray(i) = Workbooks(i).Name
    
19.             Next
    
20.     GoTo Search '·j´M¶}±Òªºreport¦WºÙ
    
21.             
    
22. Search: 'find report name contains wkabc
    
23.        searchTerm = Wkabc & "¤ÀªR"
    
24.     'Check if a value exists in the Array
    
25.     If UBound(Filter(myArray, searchTerm)) >= 0 And searchTerm <> "" Then
    
26.         GoTo Filter1
    
27.     Else
    
28.         MsgBox "§ä¤£¨ì¦¹³ø§i¡A½T»{¬O§_¿é¤J¥¿½T¨Ã½T»{Àɮפw¸g¶}±Ò"
    
29.         '­Y§ä¤£¨ì´N°±¤î
    
30.     GoTo Weeklyvip
    
31.     End If
    
32. 
    
33. Filter1:
    
34.     mytype = Array("Absent", "Client", "Computer", "Connection", "Consultant Shortage", "Disconnection", "Disconnection(Idle)", "Headset", "Instant Break (Disconnection)", "Other", "Power Outage", "System", "TE")
    
35. '­p¼Æmyi 13 ¦¸
    
36.           For myi = 0 To 12
    
37. '©w¸qMYX¬°°}¦C mytpe + MYI¦¸¼Æ(§Y¦ì¸m)
    
38.           myx = (mytype(myi))
    
39. 
    
40. ' 'Filter
    
41.    Workbooks(Filename).Activate
    
42.    mySubtotal = D10
    
43.    Worksheets("¤ÀªR").Range("$A$1:$J$250").AutoFilter Field:=9, Criteria1:=myx
    
44.     'Count range
    
45.     mySubtotal = Application.WorksheetFunction.Subtotal(3, Worksheets("¤ÀªR").Range("B2:B250"))
    
46.          Windows("Weekly Refund report letter.xls").Activate
    
47.     Range("D" & myi + 10) = mySubtotal
    
48. 
    
49. Next myi
    
50.     Workbooks(Filename).Activate
    
51.     ActiveSheet.Range("$A$1:$J$250").AutoFilter Field:=9 '¶}¦^¿z¿ïALL

½Æ»s¥N½X

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

cslinmiso

·PÁÂGBKEE¦A¦¸´£ÂI¡A¸Õ¥Î«áªº½T¬O°õ¦æ¥¿±`¡C§¹¬üµL¯Ê¡C
¯u¤£ª¾¹D¤p§Ì·í®É«ç»ò·|¤@ª½°õ¦æ«á±o¨ìªÅ¥Õ¡H

·PÁ±zªº­×¥¿¡A¬Ý¨£±z±N°}¦C§ï¦¨for each¡A½Ð°Ý¦¹ºØ¼gªk¬O§_¤ñ¸ûª½Ä±©ÎªÌ¤ñ¸û²³æ©O¡H
¤p§ÌÁÙ¬O·s¤â±`±`¶¤F¤@¤j°é¡C

¤µ¤éÁÙ¦³¼g¤F´X­ÓÁÙ±o³Ò¾r¦U¦ì¬Ý¬Ý¡A¤£¹L²{¦b¤w¸g¦b®a¤F¡A©ú¤Ñ¦A³Ò·Ð¦U¦ì¡C

¦A¦¸·PÁÂGBKEEª©¥D¥H¤ÎHsieh ¶Wª©.hugh0620 ^_^

hugh0620

¦^´_ 7# cslinmiso


    ¼P~ §Ú¦³¤U¸ü§AªºÀÉ®×~ °õ¦æ«á~ ¨Ã¥¼¦³±z»¡ªº°ÝÃD­C~ ¨þ¨þ~

   ¤£¹L¨SÃö«Y~ ¦³G¤j¸òH¤j~ À°§A¸Ñ¨M¤F~ §Aªº°ÝÃD~ ^^

   ¨ä¹ê¤£ºÞ¬OG¤j¤jªº¼gªk©Î¬OH¤j¤jªº¼gªk
   ³o­Ó­n¬Ý­Ó¤Hªº¨Ï¥Î²ßºD~  ±z³£¥i¥H°Ñ¦Ò¬Ý¬Ý~~

   ¤£¹L­n´£¿ôªº¬O~ ¸ê®Æ¿é¤J®É¤é´Á©Î¬O®É¶¡ªº®æ¦¡~ ³q±`³£·|³y¦¨~
   ¨â­Ó¤£¦PSHEET¦b¤ñ¹ï®É~ µLªk±N¸ê®Æ¥¿½T¤ñ¹ï~

GBKEE

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

cslinmiso

¦^´_ 15# GBKEE

ÁÂÁÂGBKEEª©¥D´£ÂI¡A¸Õ¥Î±zªºµ{¦¡«á¥¿±`¡C
·Q½Ð°Ý§Ú­ì¥»ªºµ{¦¡¬O¤ñ¸û¤£¦nÁÙ¬O·|³y¦¨°ÝÃD©O?

hugh0620 ¤Q¤À·PÁ±zªºÀ°§U¡A§Ú·|§l¦¬±zªº«Øij

·PÁ¦U¦ì¡A¤µ«á¦³°ÝÃD§Ú·Q§ÚÀ³¸Óª½±µ±µµÛ³o½gÄ~Äò°Ý¦n¤F(¦pªG¬ÛÃöªº¸Ü)