VBA Excel Do VBA语句

Question

我试图确定的运行时间的设备在一个班次，这是输出的以下形式；

    27/01/2016 18:00:00         4
    28/01/2016 6:00:00         12
    28/01/2016 18:00:00         4
    29/01/2016 6:00:00          0

宏的目标是向下循环列，记录班次的开始时间和结束时间。

    28/01/2016 02:00 to 28/01/2016 22:00:00. 

如果块由连续小时组成，则为零。

然而，如果在时间上有一个间隔，比如在第二个4小时以下，就变成了一个单独的块：

    27/01/2016 18:00:00         4
    28/01/2016 6:00:00         12
    28/01/2016 18:00:00         0
    29/01/2016 6:00:00          4

我的当前代码如下所示

  Sub EX01()

Dim ColCount As Long
Dim startrow, endrow As Long
Dim i, j As Long
Dim nextRow As Long
Dim Cumm As Double


Set wb = ThisWorkbook
Set ws = wb.Sheets("XACT RE")
Set ws3 = wb.Sheets("RE_EX 01")

ws.Select




startrow = Cells(1, 2).Value
endrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row - 1
Cells(1, 28) = startrow
Cells(1, 29) = endrow

nextRow = 2 'this is the row to put info on RE sheet

For j = 3 To 6
For i = startrow To endrow

    Cumm = 0
    If Cells(i, j) <> 0 Then

        'this is the inital pickup row
            ws3.Cells(nextRow, 1) = ws.Cells(i + 1, 2) - (ws.Cells(i, j).Value / 24) 'get next row time and subtract the hours in this cell
            ws3.Cells(nextRow, 3) = ws.Cells(3, j)
            Cumm = Cumm + ws.Cells(i, j).Value

            'now check how long the run goes for and add the delay data to Cumm
        Do While Cells(i + 1, j).Value >= 12
                        i = i + 1
            Cumm = Cumm + ws.Cells(i, j).Value


        Loop

        'this exits loop if less than 10
        ws3.Cells(nextRow, 2) = ws.Cells(i, 2).Value + ((ws.Cells(i, j).Value) * (0.5 / 12))
        ws3.Cells(nextRow, 8) = Cumm
        nextRow = nextRow + 1


    End If



  Next i
 Next j




End Sub

因此，我的问题是，如何计算12小时以下的单班倒班，以及如何解释12小时轮班后发生的小班次(少于12小时).

Answer 1

嗨，我在这方面不是很好，所以我会马上适应我所看到的东西。但是第一:开始日期/时间在哪一列，哪一列是结束日期/时间？都列在同一栏里吗？

Sub EX01()

Dim ColCount As Long    
Dim startrow As Long
Dim endrow As Long
Dim i As Long
Dim j As Long
Dim nextRow As Long
Dim Cumm As Double

我认为只在1行上声明变量是一种古老的做法。而且我觉得这不是个好习惯。如果我是正确的，这只是更好的方式来跟踪所有使用的变量。

Set wb = ThisWorkbook
Set ws = wb.Sheets("XACT RE")
Set ws3 = wb.Sheets("RE_EX 01")

ws.Activate

我最近了解到，.select从来不是一个好主意，试试解决办法吧！

'startrow = Cells(1, 2).Value
'endrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row - 1
'Cells(1, 28) = startrow

您要将单元格(B1)的值输入到单元格(AB1)中是正确的吗？如果是，请尝试如下：

ws.Cells(1,28).Value = ws.Cells(1,2).Value
'Cells(1, 29) = endrow

startrow = ws.Rows(1)
endrow = ws.Cells(ws.Rows.count, "a").End(xlUp).Row  'this will give you the last entry on this worksheet

'nextRow = 2

我相信你想要的是：

nextRow = (ws3.Cells(ws3.Rows.Count, "a").End(xlUp).Row) + 1

For j = 3 To 6
    For i = startrow To endrow
        ws3.Cells(nextRow, 1) = ws.Cells(i + 1, 2) - (ws.Cells(i, j).Value / 24) 'get next row time and subtract the hours in this cell
        ws3.Cells(nextRow, 3) = ws.Cells(3, j)
        Cumm = Cumm + ws.Cells(i, j).Value

你想把Cumm印在什么地方吗？否则它就会流过..。另外，您已经完成了这1行代码上的操作！

        Do While ws.Cells(i + 1, j).Value >= 12
            i = i + 1
            Cumm = Cumm + ws.Cells(i, j).Value
        Loop

循环将退出，只要在搜索单元格的下一行中有一个值"12“。

        ws3.Cells(nextRow, 2) = ws.Cells(i, 2).Value + ((ws.Cells(i, j).Value) * (0.5 / 12))
        ws3.Cells(nextRow, 8) = Cumm
        nextRow = nextRow + 1

    Next i
Next j

End Sub

此外，也要非常具体地说明您从哪个工作表中插入单元格。有了更多的信息，我可能会帮你一些更好的:)