如何每天将日期添加到第一个空白行

Question

我希望每天，当我打开excel，我的excel会添加一个今天的日期到它。我有这段代码，但它不起作用，有时它能做它应该做的事情，有时它跳过一行，有什么帮助吗？

Sub Stretching()
'This procedure will run each time you open the workbook

   'Specify the required worksheet name in double quotes
    Dim ws As Worksheet
    Set ws = Sheets("Stretching")

    'Get the last row number filled with a value in Column A
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    'Check if the last entered date is the same as the current date, if so, exit
    'You need this check to see if you close the workbook then open it on the same day
    'so that the code does not enter the same date again in a new row.
    If ws.Cells(lastRow, 1).Value = Date Then Exit Sub

    'Fill a new row in Column A with the current date
    If IsEmpty(Cells(lastRow, 1)) Then
        ws.Cells(lastRow, 1).Value = Date
    Else
        ws.Cells(lastRow, 1).Offset(1, 0).Value = Date

    End If
End Sub

Answer 1

关于您的代码的一些建议：

1. 完全限定了范围，帮助您避免结果不一致。这意味着，当活动工作表与您所针对的工作表不同时，您可以运行该过程，这一行：Cells(Rows.Count, 1).End(xlUp).Row将返回与您预期不同的“最后一行”，
2. 也尝试使用易于理解的变量名。例如，targetSheet

和ws

-：

Public Sub Stretching()
    'This procedure will run each time you open the workbook

   'Specify the required worksheet name in double quotes
    Dim targetSheet As Worksheet
    Set targetSheet = ThisWorkbook.Sheets("Stretching")

    'Get the last row number filled with a value in Column A
    Dim lastRow As Long
    lastRow = targetSheet.Cells(targetSheet.Rows.Count, 1).End(xlUp).Row

    'Check if the last entered date is the same as the current date, if so, exit
    'You need this check to see if you close the workbook then open it on the same day
    'so that the code does not enter the same date again in a new row.
    If targetSheet.Cells(lastRow, 1).Value = Date Then Exit Sub

    'Fill a new row in Column A with the current date
    If IsEmpty(targetSheet.Cells(lastRow, 1)) Then
        targetSheet.Cells(lastRow, 1).Value = Date
    Else
        targetSheet.Cells(lastRow, 1).Offset(1, 0).Value = Date
    End If

End Sub