Search code examples
excelvbaoffset

Use of ' in offset

I have some code, can someone explain how it works and why it works that way? Specifically this line of code:

With Sheets("Sheet2")
    .Cells(1, 1).End(xlDown).Offset(1).Value = Format(Date, "dd/mm/yyyy")

Now I get it that the code itself outputs the date in Cells(1,1) and offsets by 1 from the bottom for every subsequent entry. What I don't understand is why in the sheet itself one of the cells is blocked out?

This is what I mean by blocked out, cell A2 is filled with '--------

And then the code works fine. But if cell A2 doesn't have '-------- it suddenly doesn't work anymore and gives me Application defined, object defined error. Can someone explain what '-------- does here and why its used?

Solution

  • The idea of the code is: "Jump to the last row that is used in column A, go down 1 row and write the date into it.

    What it does: Go (virtually) to cell A1 and press (also virtually) the key Ctrl+Down. This jumps to the last used cell of a column - but only if there is more than one cell filled, else it will jump to the very last row of the sheet (try this in Excel to understand).

    Now if you offset one row from the very last row of a sheet, Excel cannot do anything more than complain.

    The solution: Use .Cells(.rows.count, 1).End(xlUp) instead. This works the opposite way: Go to the very last row and press Ctrl+Up. For more details how to find the last used cell, see Error in finding last used cell in Excel with VBA

    What you also should do:
    Don't cascade things, use intermediate variables, that helps debugging.
    Don't write a date as string (format converts a date into a string). Write the date as date and set the number format of the cell.

    With Sheets("Sheet2")
    Dim lastCell as Range
    Set lastCell = .Cells(.rows.count, 1).End(xlUp)
    lastCell.Offset(1).Value = Date
    ' If needed:
    ' lastCell.Offset(1).NumberFormat = "dd/mm/yyyy"
End With