Runtime Error - Method 'Cells' of Object'_Global' Failed

[Guest]

We have an Excel macro that was written several versions of Excel back (by
someone who is no longer with the company) which is now showing a runtime
error - Method 'Cells" of Object'_Global Failed when run by Excel 2003. The
line in the code that creates the error is written as Cells.(1, 1).Select. At
first the code failed in Excel 2002 as well, but I created a new module and
copied the code in from a working clone of the workbook (after removing the
old module) and it seemed to correct the problem. When I returned the file to
the Excel 2003 user, it failed.

I'm anticipating seeing this failure come up in the future as we continued
to update to 2003 and eventually 2007. I would appreciate any guidance as to
whether the syntax of the Cells.(1, 1).Select line should be changed (and to
what), or what else I should be looking at to correct the problem.

Thank you, in advance, for any help you can provide.

 

[Jon Peltier]

Cells.(1, 1).Select

should be

Cells(1, 1).Select

To make it more robust, you should specify a sheet in front of Cells,
something like:

ActiveWorkbook.Worksheets("Sheet1").Cells(1, 1).Select

Generally Excel assumes the active sheet, but if the active sheet is a chart
sheet, you're hosed. And I'd rather specify something than let Excel assume
it knows what I want.

- Jon

 

[Guest]

Jon --

Thanks so much for the quick response! I set up the code as you suggested:

Sub Trendline_Update()
'
' Trendline_Update Macro
' Macro recorded 8/30/2001

current_column = 18
current_row = 3
end_row = 15
ActiveWorkbook.Worksheets("Trendline Chart").Cells(1, 1).Select

But it stops on the ActiveWorkbook line with a "Select Method of Range Class
Failed" error.

The original code looked like this:

current_column = 18
current_row = 3
end_row = 15
Sheets("Trendline Chart").Select
Cells(1, 1).Select

Thanks,

Val

 

[Jon Peltier]

If the sheet isn't active, you can't select cells on it. Why do you need to
select the cells? What's next? You can probably adjust the code so you need
not select anything.

- Jon