返回列表 上一主題 發帖

[發問] 連續數列資料判斷問題

回復 1# cait
  1. Sub nn()
  2. Dim Ar(11), Rng As Range, cnt%, r&, A As Range, k%, t1&, s&
  3. Sheet2.Cells = ""
  4. With Sheet1
  5. r = 2: k = 1: ay = Array("班次", "連續次數", "編號", "日期", "班次", "車號", "時間", "時間轉換", "速度", "連續時間", "連續距離")
  6. Do Until r > Application.CountA(.Columns("A"))
  7. cnt = 1: t1 = .Cells(r, 6): s = .Cells(r, 7): Ar(0) = .Cells(r, 3): Set Rng = .Cells(r, 1).Resize(, 7)

  8. Do Until .Cells(r, 1) + 1 <> .Cells(r + 1, 1) Or .Cells(r, 3) <> .Cells(r + 1, 3)
  9. r = r + 1
  10. Set Rng = Union(Rng, .Cells(r, 1).Resize(, 7))
  11. cnt = cnt + 1
  12. Loop
  13. If cnt > 1 Then
  14. If Rng(1, 3) <> Sheet2.Cells(2, k) And Sheet2.[A1] <> "" Then k = k + 12
  15. Ar(1) = cnt
  16. Ar(9) = .Cells(r, 6) - t1
  17. Ar(10) = Ar(9) * s
  18. Sheet2.Cells(1, k).Resize(, 11) = ay
  19. Set A = Sheet2.Cells(65536, k + 2).End(xlUp).Offset(1, 0)
  20. Sheet2.Cells(A.Row, k).Resize(, 11) = Ar
  21. Rng.Copy Sheet2.Cells(A.Row + 1, k + 2)
  22. Erase Ar
  23. End If
  24. r = r + 1
  25. Loop
  26. End With
  27. End Sub
複製代碼
學海無涯_不恥下問

TOP

        靜思自在 : 待人退一步,愛人寬一寸,就會活得很快樂。
返回列表 上一主題