- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
回復 6# olisun
試試看- Private Sub Ex()
- Dim d As Date, W%, AD, Rng As Range, i%, ii%
- d = 工作表1.[E2]
- W = Weekday(d, vbMonday) '週一為第1天
- With 工作表1
- .[j13] = "本週 " & d - (W - 1) & " ** " & d + (5 - W)
- Set Rng = .[j15:l26]
- For i = 0 To Rng.Columns.Count - 1
- For ii = 1 To Rng.Rows.Count
- AD = Evaluate("SUMIF(" & 工作表17.[A:A].Address(, , , 1) & ",""<=" & d + (5 - W) & """, " & 工作表17.Columns(1 + (i * 12) + ii).Address(, , , 1) & ")")
- AD = AD - Evaluate("SUMIF(" & 工作表17.[A:A].Address(, , , 1) & ",""<" & d - (W - 1) & """, " & 工作表17.Columns(1 + (i * 12) + ii).Address(, , , 1) & ")")
- Rng.Cells(ii, i + 1) = AD
- Next
- Next
- Set Rng = .[M15:O15]
- For i = 1 To Rng.Columns.Count
- AD = Evaluate("SUMIF(" & 工作表18.[A:A].Address(, , , 1) & ",""<=" & d + (5 - W) & """, " & 工作表18.Columns(1 + i).Address(, , , 1) & ")")
- AD = AD - Evaluate("SUMIF(" & 工作表18.[A:A].Address(, , , 1) & ",""<" & d - (W - 1) & """, " & 工作表18.Columns(1 + i).Address(, , , 1) & ")")
- Rng.Cells(1, i) = AD
- Next
- End With
- End Sub
|
|
發表於 2010-8-28 06:09
| 
