|
发表于 2015-1-14 14:20
|
显示全部楼层
本楼为最佳答案
- Dim T1 As Boolean, T2 As Boolean, T3 As Boolean
- arr = Sheet1.[b4].CurrentRegion
- cp = [c5]: rq1 = [h5]: rq2 = [j5]
- ReDim brr(1 To UBound(arr), 1 To 12)
- For i = 2 To UBound(arr)
- If Len(arr(i, 3)) = 0 Then arr(i, 3) = arr(i - 1, 3) '出车日期
- If Len(arr(i, 8)) = 0 Then arr(i, 8) = arr(i - 1, 8) '到达日期
- T1 = False: T2 = False: T3 = False '筛选符合条件的记录
- If Len(cp) = 0 Or arr(i, 1) = cp Then T1 = True '车牌
- If Len(rq1) = 0 Or arr(i, 3) >= rq1 Then T2 = True '起始日期
- If Len(rq2) = 0 Or arr(i, 8) <= rq2 Then T3 = True '结束日期
- If T1 = True And T2 = True And T3 = True Then ' 车牌号、起始日期、结束日期都符合条件
- If arr(i, 13) = "取车" Then
- n = n + 1
- brr(n, 1) = arr(i, 3): brr(n, 2) = arr(i, 4): brr(n, 3) = arr(i, 5) '出发日期、时间、出发地
- brr(n, 9) = arr(i, 6) '出车里程
- xc = arr(i, 5) '行程
- ElseIf arr(i, 13) = "还车" Then
- brr(n, 5) = arr(i, 8): brr(n, 6) = arr(i, 9): brr(n, 7) = arr(i, 7) '到达日期、时间、目的地
- xc = xc & "→" & arr(i, 5) & "→" & arr(i, 7) '行程
- brr(n, 10) = arr(i, 10) '还车里程
- brr(n, 12) = brr(n, 10) - brr(n, 9) '本次里程数
- brr(n, 11) = xc '行程
- Else
- xc = xc & "→" & arr(i, 5)
- End If
- End If
- Next
- [b11].Resize(100, 12).ClearContents
- [b11].Resize(n, 12) = brr
- End Sub
复制代码 |
|