|
本帖最后由 hrpotter 于 2012-3-29 15:59 编辑
yvhanym 发表于 2012-3-29 15:10
时间1/时间2 地点1/地点2 - Sub test()
- Dim ar, br(), cr(), st
- Dim i As Integer, j As Integer, k As Integer
- ar = Sheet2.Range("a1").CurrentRegion
- With Sheet1
- .Range("a3:e65536").ClearContents
- st = .Range("a1").Value
- ReDim br(1 To 1000, 1 To 5)
- ReDim cr(1 To UBound(ar, 2) - 4, 1 To 5)
- For i = 5 To UBound(ar, 2)
- cr(i - 4, 1) = ar(1, i)
- For j = 2 To UBound(ar)
- If ar(j, i) = st Then
- k = k + 1
- br(k, 1) = ar(1, i)
- br(k, 3) = Format(ar(j, 2), "h:mm") & "-" & Format(ar(j, 3), "h:mm")
- br(k, 4) = ar(j, 4)
- br(k, 5) = ar(j, 1)
- End If
- Next
- Next
- For i = 1 To UBound(cr)
- cr(i, 2) = Right(Format(cr(i, 1), "aaaa"), 1)
- For j = 1 To k
- If br(j, 1) = cr(i, 1) Then
- cr(i, 3) = cr(i, 3) & "/" & br(j, 3)
- cr(i, 4) = cr(i, 4) & "/" & br(j, 4)
- cr(i, 5) = cr(i, 5) + br(j, 5)
- End If
- Next
- Next
- For i = 1 To UBound(cr)
- If cr(i, 3) = "" Then
- cr(i, 3) = "休息"
- Else
- cr(i, 3) = Right(cr(i, 3), Len(cr(i, 3)) - 1)
- cr(i, 4) = Right(cr(i, 4), Len(cr(i, 4)) - 1)
- End If
- Next
- .Range("a3").Resize(UBound(cr), 5) = cr
- End With
- End Sub
复制代码
|
|