Runtime 424 "Object required" error

  • I have some code and one of the subs contains this line:

    Do Until Sheet1.Range("A" & Row).Value = vbNullString

    It works flawlessly.

    However I now want to run this code on a different sheet, namely one called "Invoice". So I changed the code to:

    Do Until Invoice.Range("A" & Row).Value = vbNullString

    However now when I run it, I get a runtime 424 error, "Object Required".

    If I change it to:

    Do Until Worksheets("Invoice").Range("A" & Row).Value = vbNullString

    again, it works fine.

    Can anyone tell me what's going on, and why sometimes you need to use "Worksheets("<name>")" format and sometimes not?


    Also posted here:…ed-error.html#post4324572

  • Re: Runtime 424 &quot;Object required&quot; error

    Sheet1 is the CodeName of Sheets("Sheet1")

    If you make Invoice the CodeName of the sheet named "Invoice" then the code that errors will work.

    To change the CodeName open the VB Editor, click the "Invoice" worksheet in the Project window then change the first field ("(Name)") in the Properties window and press Enter.

    It is better practice to use sheet CodeNames rather than sheet names in VBA because even if the sheet name gets changed or its position in the workbook changes the code will still refer to the correct worksheet. Whereas, if Sheets("Invoice") or Sheets(2) was used in the code then they would need changing if the name of the Invoice sheet was changed or its position in worksheet order changed.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Runtime 424 &quot;Object required&quot; error

    I get the "Object required" error when trying to run the following clickbutton macro in a different madule.
    Otherwise, the button works perfectly fine.

    Public Sub Plus_Click()
    Dim str As String
    'insert a new row below the cell "Below"
    NewRow "Below"

    'trying to set str as the range name of a specific cell using Offset function
    'str = Region_1

    str = Range("Below").Offset(-2, 1)
    'Name a cell as Region_2
    ActiveWorkbook.Names.Add name:="Region_" & Right(str, 1) + 1, RefersTo:=Range("Below").Offset(-1, 1)
    End Sub

    Can I get some help?


  • Re: Runtime 424 &quot;Object required&quot; error

    Hi Alex,

    Rather than tack onto an older thread someone else started, please start your own thread. You could copy and paste the above here:

    This will help ensure that each thread has it's own unique question/answer.
    I'm closing this thread, as original question was answered last year. Thanks!

    Best Regards,
    Luke M
    "A little knowledge is a dangerous thing."

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!