|
本帖最后由 lxjiang 于 2014-1-6 00:30 编辑
Sub Macro1()
Dim r%, i%, t%, m%
Dim arr
With Sheets("Cal")
For m = 1 To 4
r = .Cells(Rows.Count, 17 + m * 2).End(xlUp).Row
arr = .Range(.Cells(4, 10), .Cells(r, 18 + m * 2))
For i = 1 To UBound(arr)
t = 0
If Len(arr(i, 8 + m * 2)) > 0 And arr(i, 9 + m * 2) >= arr(i, 8 + m * 2) Then
For j = arr(i, 8 + m * 2) To arr(i, 9 + m * 2) Step 1
If Weekday(j, 2) Like "[" & arr(i, 1) & "]" Then
t = t + 1
End If
Next
arr(i, 5 + m) = t
End If
Next
.Cells(4, 14 + m).Resize(UBound(arr), 1) = Application.Index(arr, 0, 5 + m)
.Visible = False
Next m
End With
End Sub
lxjiang 发表于 2014-1-6 10:13
谢谢 我尝试了 但是还是不行 初学 该需求工作中急用 请帮助 谢谢! - Public Sub Macro1()
- Dim r%, i%, t%, m%
- Dim arr
- Dim app As Object
- Set app = GetObject(, "excel.Application")
- With app.Worksheets("Cal")
- For m = 1 To 4
- r = .Cells(app.Rows.Count, 17 + m * 2).End(xlUp).Row
- arr = .Range(.Cells(4, 10), .Cells(r, 18 + m * 2))
- For i = 1 To UBound(arr)
- t = 0
- If Len(arr(i, 8 + m * 2)) > 0 And arr(i, 9 + m * 2) >= arr(i, 8 + m * 2) Then
- For j = arr(i, 8 + m * 2) To arr(i, 9 + m * 2) Step 1
- If Weekday(j, 2) Like "[" & arr(i, 1) & "]" Then
- t = t + 1
- End If
- Next
- arr(i, 5 + m) = t
- End If
- Next
- .Cells(4, 14 + m).Resize(UBound(arr), 1) = app.Application.Index(arr, 0, 5 + m)
- .Visible = False
- Next m
- End With
- End Sub
复制代码
|
|