excel - 如何循环地将日期从一列转换到下一列？

Question

我正在创建一个显示一天中的秒数 (86400) 的程序，这意味着从 00:00:01 到 59:59:59 将以该格式显示 86400 行数据。但是我收到运行时错误:溢出。因此，我假设我需要使用多个列来收集这些数据 - 如果是这样，我该怎么做？

Private Sub mythirdlesson()
Dim wks As Worksheet
Dim s As Integer
Dim m As Integer
Dim h As Long
Dim cellrow As Integer

Set wks = ThisWorkbook.Worksheets("Library")
cellrow = 1

For h = 0 To 59
    For m = 0 To 59
        For s = 0 To 59
        wks.Cells(cellrow, 1) = h & ":" & m & ":" & s
        cellrow = cellrow + 1
        Next
    Next
Next

End Sub

Answer 1

问题不在于 Excel 行，而在于 cellrow 数据类型。我把它改成了 long 并且没有问题。另请注意，您的脚本存在设计错误，计算一天为 60 小时而不是 24 小时。请考虑更正后的脚本:

Private Sub mythirdlesson()
    Dim wks As Worksheet
    Dim s As Integer
    Dim m As Integer
    Dim h As Long
    Dim cellrow As Long

    Set wks = ThisWorkbook.Worksheets("Library")
    cellrow = 1

    For h = 0 To 23
        For m = 0 To 59
            For s = 0 To 59
            wks.Cells(cellrow, 1) = h & ":" & m & ":" & s
            cellrow = cellrow + 1
            Next
        Next
    Next
End Sub

问候，