.Range(Cells(row,column)).Select runtime error

[Barkingdoggy]

Windows 7, Excel 2007 workbook with multiple sheets. VBA script is throwing a Runtime Error 1004, "Application-defined or object-defined error." The line in question is:

Sheets("Lunches").Range(Cells(8, 26)).Select

Using an "equivalent" statement, however, works fine.

Sheets("Lunches").Range("z8").Select

What am I missing? I want to select cells by row and column variables in this script.

Thanks!

 

[JamesW]

Hi,

Welcome to the board.

Try:

Sheets("Lunches").Cells(8,26).Select

Try to avoid .Select at all costs. Instead try something like:

Sheets("Lunches").Cells(8,26).Copy

Instead of:

Sheets("Lunches").Cells(8,26).Select
Selection.Copy

 

[Jonmo1]

Welcome to the board..

It is not necessary to enclose Cells within Range..
Unless trying to select multiple cells...

Try
Sheets("Lunches").Cells(8, 26).Select


Also, it is almost never necessary to select a range to manipulate it.
And further, this line will only work if the Sheet Lunches is ALREADY currently active.
Otherwise it will error. The sheet must be active first before you can select a cell on that sheet.
So it is essentially useless to include the sheetname on a range(or cells) when selecting it.
So simply

Cells(8, 26).Select will sufice.


Hope that helps.

 

[Barkingdoggy]

Dropping Range works, but, this is the statement I want:

Sheets("Lunches").Range(Cells(8, iColumn), Cells(iEndRow, iColumn)).Copy

It gives me the runtime error 1004. I was trying to debug it with the select statements above (which gave the same error). iColumn is set properly, e.g 26 and iEndRow is also set proplerly at e.g 165.

 

[Jonmo1]

This is where this comes into play

It is not necessary to enclose Cells within Range..
Unless trying to select multiple cells...

When you specify the sheet on the Range, you MUST also specify the sheet on BOTH Cells..

like so..

Sheets("Lunches").Range(Sheets("Lunches").Cells(8, iColumn), Sheets("Lunches").Cells(iEndRow, iColumn)).Copy

To make it easier, so you're not writing the same sheetname over and over...
Use WITH

With Sheets("Lunches")
    .Range(.Cells(8, iColumn), .Cells(iEndRow, iColumn)).Copy
End With

Notice the period preceding Range and Cells..
That tells it to use the object referenced in the With.


Hope that helps.

 

[Barkingdoggy]

That did the trick. Thank you very much!