On Mar 21, 8:59 am, "IanKR" wrote:
Hello, folks. I'm getting an error : "Run-time error '1004':
Application-defined or object-defined error" when I get to the
following line:

Set ActualsActualsRange = ActiveWorkbook.Worksheets("Actuals-
Sheet").Range(Cells(5, 12), Cells(5, 500))

Any idea what's wrong with this? It looks like a properly-formed
range, and pretty explicitly referenced.

Which version of Excel are you running? I use Excel 2003 and there are
only
256 columns in a worksheet, so I get that error because a range with a
cell
in column 500 at its bottom right corner is impossible. But perhaps you're
using Excel 2007 and that might have enough columns on a worksheet, in
which
case it's something else that's causing the problem...
I'm in 2007, so the address should be ok. Thanks for the info though!
I'll have to be careful if I export it to the old format.

If I change the 500 to 256 or less it works for me, so I guess it should
work for you in Excel 2007 at 500.

Hi Ian KR,

I'm guessing that N L is getting the error because the code is being
run from some other worksheet, or maybe some other workbook, not from
a worksheet named "Actuals-Sheet".
If you are getting the code to work you must be running it from a
worksheet named "Actuals-Sheet". Cells on its own assumes Cells in the
active worksheet, so when run from the sheet named "Actuals-Sheet"
there is no problem. When run from some other worksheet silly Excel
grinds to a halt because Cells hasn't been told that the cells are in
another worksheet. To stop this happening Cells needs to be prefaced
with the worksheet containing the cells...

Worksheets("Actuals-Sheet').Cells

This has to be done for both Cells, making the code line much longer,
so it is usually done using a With/End With.

Ken Johnson