Run-time error '1004' when attempting to insert date

Question

I have this script, I keep getting an error: Run-time error '1004': Method 'Range' of object _'Worksheet' failed

Here is the script:

Option Explicit
    Sub ONJL()

    Dim lastrow As Long
    Dim wsRD As Worksheet 'Raw Data

    Set wsRD = Sheets("Raw Data")

        With wsRD
            Application.ScreenUpdating = False
            lastrow = wsRD.Range("J:J").End(xlUp).Offset(1, 0)
            wsRD.Range("J" & lastrow).Value = "=Today()"
        End With

    End Sub

I thought this is how you are meant to look up last row, and then paste today's date on the last cell?

In your statement assigning `lastrow`, your range seems strange to me. I've seen usage of this function before and the calls appear more like this: `Sheet.Range("A656")`. Perhaps it's confused because you're giving 2 column values as the range? — gkiar, Jul 09 '12 at 15:33
see this on how to get the lastrow correctly. :) http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba — Siddharth Rout, Jul 09 '12 at 15:39

assylias · Accepted Answer · 2012-07-09T17:24:50.190

2

There are several issues:

when you use with, you need to reference the object with .
the result of Offset(x,y) is a range, not a number - you are interested in the row, so you need to add .Row
the End(xlUp) thingy has to start from the last row to work

This is probably what you meant:

Sub ONJL()

    Dim lastrow As Long
    Dim wsRD As Worksheet 'Raw Data

    Set wsRD = Sheets("Raw Data")

    With wsRD
        Application.ScreenUpdating = False
        lastrow = .Range("J" & .Rows.Count).End(xlUp).Row
        .Range("J" & lastrow + 1).Formula = "=Today()"
    End With

End Sub

And don't forget to turn the screenupdating on somewhere.

edited Jul 09 '12 at 17:24

answered Jul 09 '12 at 15:37

assylias

321,522
82
660
783

+1 but I think it should be `.Formula`, not `.Value`. Or you could put `.Value = Date` if the OP wanted a static date instead of a formula. – JimmyPena Jul 09 '12 at 16:04
I have screen updating further down the script... but I hate to say this, but the script isn't updating anything. It runs without errors, but the cell in question isn't populated with today's date. Help please? – Matt Ridge Jul 09 '12 at 16:44
If I create a new workbook, rename a tab to "Raw Data", populate some random data in column J and run the macro in my answer, it populates the date in the first available row. Either you mistyped the code or your problem comes from another place in your code. – assylias Jul 09 '12 at 16:46
@MattRidge It worked for me, the same as the code in my answer. – JimmyPena Jul 09 '12 at 16:47
@MattRidge Have you tried to run your code in debug mode (line by line) to see what goes wrong? (And remove the sreenupdating thing when you debug to make sure you do see the changes!) – assylias Jul 09 '12 at 17:10
What is the value of lastrow? – assylias Jul 09 '12 at 17:12
That's what your original code would give. Are you sure you are using the code in my answer? – assylias Jul 09 '12 at 17:23
`lastrow = .Range("J" & .Rows.Count).End(xlUp).Row` – assylias Jul 09 '12 at 17:24
Yes, I just placed your code into mine, and I am still getting 0, and I am running this step by step (F8) – Matt Ridge Jul 09 '12 at 17:24
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/13627/discussion-between-assylias-and-matt-ridge) – assylias Jul 09 '12 at 17:25
It's been solved, partly was my computer having a hissyfit, but this works perfectly now :) – Matt Ridge Jul 09 '12 at 17:46

score 2 · Answer 2 · answered Jul 09 '12 at 16:01

2

lastrow is declared as a Long variable, but Offset returns a Range object. The Row Property gives you the numeric value of the row.

Sub ONJL()

  Dim lastrow As Long
  Dim wsRD As Worksheet  'Raw Data

  Set wsRD = Sheets("Raw Data")

  With wsRD
    Application.ScreenUpdating = False
    lastrow = .Cells(Rows.Count, 10).End(xlUp).Offset(1, 0).Row
    .Range("J" & lastrow).Formula = "=Today()"
  End With

Application.ScreenUpdating = True

End Sub

answered Jul 09 '12 at 16:01

JimmyPena

8,694
6
43
64

This doesn't update the cell in J... it doesn't even overwrite anything either. I've tried this script, and it runs, it just shows no output... help please? – Matt Ridge Jul 09 '12 at 16:45
Tested and it worked for me. Every time I run it, it puts the formula into the next available cell in column J. Do you have calculation set to Manual? – JimmyPena Jul 09 '12 at 16:46
Auto, because the screenupdating = True after the end of the script. – Matt Ridge Jul 09 '12 at 16:53
I wasn't aware that changing the `ScreenUpdating` Property also changes Calculation mode. – JimmyPena Jul 09 '12 at 17:00
Me neither just stating that I do have it correct itself at the end. – Matt Ridge Jul 09 '12 at 17:04
Just tried it (again). I did the same thing [assylias](http://stackoverflow.com/questions/11398230/run-time-error-1004-when-attempting-to-insert-date/11398838#comment15029537_11398411) did and it worked. – JimmyPena Jul 09 '12 at 17:08
ScreenUpdating is not related to the calculation mode. – assylias Jul 09 '12 at 17:45

Run-time error '1004' when attempting to insert date

2 Answers2