ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¦p¦ó´M§ä¬Û¦P¸ê®Æ¨Ã½Æ»s¨ä¥LÄæ¦ì

¦^´_ 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)

-------
¥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. Application.ScreenUpdating = False 'Ãö³¬¿Ã¹õ§ó·s

  11. Wkabc = Application.InputBox(prompt:="Please Input Current Week", Title:="Weekly Report Letter for ABC")
  12.    Filename = "Week " & Wkabc & "¤ÀªR" & ".XLS" 'report¦WºÙ§ó§ï¶g¼Æ
  13. '·j´M¶}±Òªºreport¦WºÙ
  14.     For i = 1 To Workbooks.Count
  15.        'Arr = Array(Workbooks(i).Name)
  16.          myArray(i) = Workbooks(i).Name
  17.             Next
  18.     GoTo Search '·j´M¶}±Òªºreport¦WºÙ
  19.             
  20. Search: 'find report name contains wkabc
  21.        searchTerm = Wkabc & "¤ÀªR"
  22.     'Check if a value exists in the Array
  23.     If UBound(Filter(myArray, searchTerm)) >= 0 And searchTerm <> "" Then
  24.         GoTo Filter1
  25.     Else
  26.         MsgBox "§ä¤£¨ì¦¹³ø§i¡A½T»{¬O§_¿é¤J¥¿½T¨Ã½T»{Àɮפw¸g¶}±Ò"
  27.         '­Y§ä¤£¨ì´N°±¤î
  28.     GoTo Weeklyvip
  29.     End If

  30. Filter1:
  31.     mytype = Array("Absent", "Client", "Computer", "Connection", "Consultant Shortage", "Disconnection", "Disconnection(Idle)", "Headset", "Instant Break (Disconnection)", "Other", "Power Outage", "System", "TE")
  32. '­p¼Æmyi 13 ¦¸
  33.           For myi = 0 To 12
  34. '©w¸qMYX¬°°}¦C mytpe + MYI¦¸¼Æ(§Y¦ì¸m)
  35.           myx = (mytype(myi))

  36. ' 'Filter
  37.    Workbooks(Filename).Activate
  38.    mySubtotal = D10
  39.    Worksheets("¤ÀªR").Range("$A$1:$J$250").AutoFilter Field:=9, Criteria1:=myx
  40.     'Count range
  41.     mySubtotal = Application.WorksheetFunction.Subtotal(3, Worksheets("¤ÀªR").Range("B2:B250"))
  42.          Windows("Weekly Refund report letter.xls").Activate
  43.     Range("D" & myi + 10) = mySubtotal

  44. Next myi
  45.     Workbooks(Filename).Activate
  46.     ActiveSheet.Range("$A$1:$J$250").AutoFilter Field:=9 '¶}¦^¿z¿ïALL
½Æ»s¥N½X
«Ü¦h¨Æ±¡¡A¶}©l°µ¤F¤§«á¤~µo²{«Ü²³æ¡A
¯u¥¿Ãøªº¬O«ç»ò§¹¬ü¦aµo´§¦Û¨­ªºª¬ºA¡C

TOP

¦^´_ 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. Dim Filename, Wo As Workbook, Filename_Msg As Boolean
  12. Application.ScreenUpdating = False 'Ãö³¬¿Ã¹õ§ó·s
  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 Each Wo In Workbooks
  17.          If Wo.Name = Filename Then
  18.             Filename_Msg = True
  19.             Exit For
  20.          End If
  21.     Next
  22.     If Filename_Msg = False Then
  23.         MsgBox "§ä¤£¨ì¦¹³ø§i¡A½T»{¬O§_¿é¤J¥¿½T¨Ã½T»{Àɮפw¸g¶}±Ò"
  24.         Exit Sub
  25.     End If
  26.      
  27. Filter1:
  28.     mytype = Array("Absent", "Client", "Computer", "Connection", "Consultant Shortage", "Disconnection", "Disconnection(Idle)", "Headset", "Instant Break (Disconnection)", "Other", "Power Outage", "System", "TE")
  29.     For myi = 0 To 12 '­p¼Æmyi 13 ¦¸
  30. ' 'Filter
  31.         Workbooks(Filename).Activate
  32.         'mySubtotal = D10      '¿ù»~ ¨S¦³³]¥ßÅܼÆ
  33.         Worksheets("¤ÀªR").Range("$A$1:$J$250").AutoFilter Field:=9, Criteria1:=mytype(myi)
  34.             'Count range
  35.         mySubtotal = Application.WorksheetFunction.Subtotal(3, Worksheets("¤ÀªR").Range("B2:B250"))
  36.         Windows("Weekly Refund report letter.xls").Activate
  37.         Range("D" & myi + 10) = mySubtotal
  38.     Next myi
  39.     Workbooks(Filename).Activate
  40.     ActiveSheet.Range("$A$1:$J$250").AutoFilter Field:=9 '¶}¦^¿z¿ïALL
  41. End Sub
½Æ»s¥N½X

TOP

·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 ^_^
«Ü¦h¨Æ±¡¡A¶}©l°µ¤F¤§«á¤~µo²{«Ü²³æ¡A
¯u¥¿Ãøªº¬O«ç»ò§¹¬ü¦aµo´§¦Û¨­ªºª¬ºA¡C

TOP

¥»©«³Ì«á¥Ñ hugh0620 ©ó 2012-10-2 21:03 ½s¿è

¦^´_ 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¤ñ¹ï~
¾Ç²ß¤~¯à´£¤É¦Û¤v

TOP

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

TOP

¦^´_ 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¬ÛÃöªº¸Ü)
«Ü¦h¨Æ±¡¡A¶}©l°µ¤F¤§«á¤~µo²{«Ü²³æ¡A
¯u¥¿Ãøªº¬O«ç»ò§¹¬ü¦aµo´§¦Û¨­ªºª¬ºA¡C

TOP

        ÀR«ä¦Û¦b : «Î¼e¤£¦p¤ß¼e¡C
ªð¦^¦Cªí ¤W¤@¥DÃD