What causes this runtime 424 "Object Required" error?

[gers1978]

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 too:

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?

Thanks!

Also posted here: http://www.ozgrid.com/forum/showthread.php?t=197330&p=759134#post759134

 

[mole999]

Sheet1.Range is an unnamed sheet, first in the book

Worksheets("Invoice") identifies a sheet called Invoice

Invoice.Range would look for a named range called invoice

I use sheets("Invoice") normally, i don't know what the difference between sheets and worksheets actually is

 

[shg]

Look in the Project Explorer window. Under Microsoft Excel Objects, you will see a module called ThisWorkbook (unless you've changed the name) and a module for each worksheet. The worksheet names appear in the list as CodeName (SheetName). In a new workbook, it would appear as Sheet1 (Sheet1).

If you change the name of Sheet1 to Invoice, it would appear as Sheet1 (Invoice).

The construct Sheet1.whatever must use the codename of the worksheet. The construct Worksheets("Invoice") must use the sheet name.

@mole:

i don't know what the difference between sheets and worksheets actually is

The Sheets collection includes worksheet, chart sheets, macro sheets, and some other type of sheet I can't remember.

 

[mole999]

The Sheets collection includes worksheet, chart sheets, macro sheets, and some other type of sheet I can't remember.

That's more understandable

 

[shg]

... and some other type of sheet I can't remember.

Dialog sheets.

Macro sheets and dialog sheets are long deprecated.

 

[RoryA]

And Excel 95 modules.

 

[shg]

Is that different from XL4 macros sheets, Rory?

 

[RoryA]

Yep. It was the first VBA module.

 

[shg]

What's the XlSheetType enumeration?

 

[RoryA]

It doesn't have one. You add it using Modules.Add