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?
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?
CodePudding user response:
When there is no value in A2
, it will give you an error because End(xlDown)
from A1
will take you to the last cell A65536
in Excel 2003
and A1048576
in Excel 2007
. It is like selecting cell A1
and manually pressing the End
key and then the Down
arrow key. And of course you can't offset down from the last cell and hence you are getting the Error 1004 - Application-defined or Object-defined error
.
I recommend using xlUp
to find the last row and then enter the data as shown HERE
Here is an example
Dim lRow As Long
With Sheets("Sheet2")
lRow = .Range("A" & .Rows.Count).End(xlUp).Row 1
.Cells(lRow, 1).Value = Format(Date, "dd/mm/yyyy")
End With
CodePudding user response:
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