GBKEE

¦^´_ 10# olisun   
07# µ{¦¡½X¤¤
Set Rng = .[j15:l26]  §ï¦¨ Set Rng = .[Q15:Q26]
§R±¼ 11.                AD = AD - Evaluate("SUMIF(" & ¤u§@ªí17.[A:A].Address(, , , 1) & ",""<" & d - (W - 1) & """, " & ¤u§@ªí17.Columns(1 + (i * 12) + ii).Address(, , , 1) & ")")
´N¥i¥H

olisun

¥i¯à¦³ÂI»~·|¡A¦]¬°­ì¨ÓªºP15:P26¬O¬£³ø¡AQ15:Q26¬O§¨³ø
¦b"¬£§¨³ø«Å¶Ç¨®"¤¤¤é´Á¬OA:A     ¬£³ø¬OB:M    §¨³ø¬ON:Y
©Ò¥H§Ú¤U¦C¦¡¤l¦bP15:P26¬O¹ïªº¡A¥i¬OQ15:Q26§Ú´N¤£·|³]¤F
Private Sub CommandButton2_Click()
    With Sheets("¤é³øªí")
             Set c = Sheets("¬£§¨³ø«Å¶Ç¨®").Range("a:a").Find(.[E2], lookat:=xlWhole)
              Arr = Sheets("¬£§¨³ø«Å¶Ç¨®").[a3].Resize(c.Row - 2, 13)
        For i = 15 To 26
      Sheets("¤é³øªí").Range("p" & i).Value = Application.Sum(Application.Index(Arr, 0, i - 13))
            Next i
   End With
End Sub

Hsieh

¦^´_ 13# olisun
²Ö­p°Ï¶ô­pºâ

1. Sub ²Ö­p()
   
2. Dim d as Object,sh as WorkSheet,r as Long,a as Range,w As String,dt As Date,b As Range,at As String
   
3. Set d = CreateObject("Scripting.Dictionary") '«Ø¥ß¦r¨åª«¥ó
   
4. For Each sh In Sheets(Array("¬£§¨³ø«Å¶Ç¨®", "NP¡BCF")) '¦b2­Ó¸ê®Æ¤u§@ªí´`Àô
   
5. With sh
   
6. r = 3
   
7. dt = .Cells(r, 1) '¸ê®Æ¤u§@ªíAÄ檺¤é´Á
   
8.   Do Until dt > Sheets("¤é³øªí").[E2]  '·í¤é´Á¶W¹L´N¸õÂ÷°j°é
   
9.      For Each a In .Range(.[B2], .[B2].End(xlToRight))  '¸ê®Æ¤u§@ªí²Ä2¦C°µ´`Àô
   
10.         w = a.Offset(-1, 0).MergeArea.Cells(1, 1)  '²Ä¤@¦C¹ïÀ³¨ìªº¤º®e¦]¬°¦³¦X¨ÖÀx¦s®æ©Ò¥H¨ú¦X¨Ö½d³ò²Ä¤@®æªº­È¼g¤JÅܼÆ
    
11.         d(w & a) = d(w & a) + .Cells(r, a.Column).Value  '¥HºØÃþ¸ò°Ï°ì¦r¦ê°µ¯Á¤ÞÀx¦s¹ïÀ³¨ìªº­È
    
12.      Next
    
13.      r = r + 1
    
14.      dt = .Cells(r, 1)  '¼g¤J¤U¤@­Ó¤é´Á
    
15.   Loop
    
16.   End With
    
17. Next
    
18. With Sheets("¤é³øªí")
    
19. For Each a In .[B15:B26]
    
20. at = a
    
21.    For Each b In .[P14:U14]
    
22.       If b.Column > 18 Then at = ""  '¦]¬°18Äæ¥H«á¨S¦³¦a°Ï©Ò¥H­nÅý¦a°ÏÅܼÆÅܪÅ
    
23.       .Cells(a.Row, b.Column) = d(b & at)  '¼g¦^¤é³øªí
    
24.    Next
    
25. Next
    
26. End With
    
27. End Sub

½Æ»s¥N½X

olisun

¦^´_  olisun
²Ö­p°Ï¶ô­pºâ
Hsieh µoªí©ó 2010-8-30 22:48

    µª®×¦³°ÝÃD¡A¦pªG§Ú¿é¤J¤é´Á¬O2010/8/5¡A«e­±¦³¤@µ§¬O2010/8/4¡A«á­±¤@µ§¬O2010/8/6¡A
¥L²Î­pªº¬O2010/8/4+2010/8/6¡A¦Ó¤£¬O2010/8/4+2010/8/5

Hsieh

¦^´_ 15# olisun
¦pªG§A±Ô­z¨S¿ù¨º´N¬Oµ§»~¦h¤@¦ær=r+1(¤w­×¥¿)
¦pªG¬O¦P¤@©P²Ö­p

1. Sub ¦P©P²Ö­p()
   
2. Set d = CreateObject("Scripting.Dictionary")
   
3. For Each sh In Sheets(Array("¬£§¨³ø«Å¶Ç¨®", "NP¡BCF"))
   
4. With sh
   
5. r = 3
   
6.   Do Until .Cells(r, 1) = "" 'Sheets("¤é³øªí").[E2]
   
7.      dt = .Cells(r, 1)
   
8.      If Year(dt) = Year(Sheets("¤é³øªí").[E2]) And Application.WeekNum(dt, 2) = Application.WeekNum(Sheets("¤é³øªí").[E2], 2) Then
   
9.      For Each a In .Range(.[B2], .[B2].End(xlToRight))
   
10.         w = a.Offset(-1, 0).MergeArea.Cells(1, 1)
    
11.         d(w & a) = d(w & a) + .Cells(r, a.Column).Value
    
12.      Next
    
13.      End If
    
14.      r = r + 1
    
15.      dt = .Cells(r, 1)
    
16.   Loop
    
17.   End With
    
18. Next
    
19. With Sheets("¤é³øªí")
    
20. For Each a In .[B15:B26]
    
21. at = a
    
22.    For Each b In .[P14:U14]
    
23.       If b.Column > 18 Then at = ""
    
24.       .Cells(a.Row, b.Column) = d(b & at)
    
25.    Next
    
26. Next
    
27. End With

½Æ»s¥N½X

olisun

¦]¬°§Ú®M¦b§Úªº¦¡¤l¸Ì¡A¦Ó§Ú¦¡¤l¤¤¦³Dim d As Date³o¤@¥y¡A©Ò¥H§Ú§ï¦¨
Set g = CreateObject("Scripting.Dictionary")
¦ý¬O¥X²{«¬ºA¤£²Å¦X

olisun

¤W¤@­Ó°ÝÃD¬O§Údim¦³¥[¤F¤@¥ydim g as range¡A§ï¤F¤§«á¤S¥X²{¥t¤@­Ó
Set g = CreateObject("Scripting.Dictionary")
For Each sh In Sheets(Array("¬£§¨³ø«Å¶Ç¨®", "NP¡BCF"))
With sh
R = 3
gt = .Cells(R, 1)
  Do Until gt > Sheets("¤é³øªí").[E2]
     For Each a In .Range(.[B2], .[B2].End(xlToRight))
        W = a.Offset(-1, 0).MergeArea.Cells(1, 1)     ----------- «¬ºA¤£²Å
        g(W & a) = g(W & a) + .Cells(R, a.Column).Value
     Next
     R = R + 1
     dt = .Cells(R, 1)
  Loop
  End With
Next
With Sheets("¤é³øªí")
For Each a In .[B15:B26]
at = a
   For Each b In .[P14:U14]
      If b.Column > 18 Then at = ""
      .Cells(a.Row, b.Column) = g(b & at)
   Next
Next
End With

olisun

·PÁ¡A³o¦¸ok