Error 1004 on VBA operation

[N L]

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.

Thanks,
N L

 

[Joel]

Make sure the worksheet name is spelled exactly the same as the macro.

 

[IanKR]

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...

 

[Ken Johnson]

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.

Thanks,
N L

Hi NL,
try...

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

Cells assumes ActiveWorksheet so you needed to preface the Cells with
the applicable worksheet name. This is most efficiently done with a
With/End With.
Don't miss the "." before each Cells.

Ken Johnson

 

[N L]

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.

 

[IanKR]

Hello, folks. I'm getting an error : "Run-time error '1004':

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...

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.

 

[Ken Johnson]

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

 

[IanKR]

Hello, folks. I'm getting an error : "Run-time error '1004':

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.

You're absolutely right. I'm running it from a normal code module, but it
only works if "Actuals-Sheet" is the active sheet, otherwise it throws up
that error. If I precede it with:
ActiveWorkbook.Worksheets("Actuals-Sheet").Activate
it works fine, whichever is the active sheet to start with. So, the sheet in
question has to be the active one. (Of course, because I'm running XL2003, I
have to change the second Cells(...) value to put the range inside 256
columns.)

So I guess that N L has to first activate the relevant workbook (if not
already) and then the activate the "Actuals-Sheet" worksheet before (s)he
can set the range reference. The annoying/irritating thing is that, at first
sight, N L's code does put the Range unambiguously in the right sheet, since
Range is qualified with the relevant sheet, but (as you say in your other
post) you ALSO have to qualify Cells(...) with the relevant sheet as well!

Doesn't Excel VBA work in mysterious ways!

 

[N L]

Doesn't Excel VBA work in mysterious ways!

It sure does. It seemed to be that directly, explicitly referencing
the sheet I wanted to specify by putting it in quotes:
Set ActualsActualsRange = ActiveWorkbook.Worksheets("Actuals-
Sheet").Range(Cells(5, 12), Cells(5, 500))
...it would have been okay.
I think you're right that Excel is ignoring my direct reference to
Worksheets("Actuals-Sheet"), because this does work if I'm already IN
Actuals-Sheet. The trick is learning through experience where VBA does
what you think it should do, and where it follows its own rules.

I've since gone about the entire problem a different way, and I'm
running all of the code in an entirely separate workbook.

Thanks again.
N L

 

[N L]

Make sure the worksheet name is spelled exactly the same as the macro.

I double and triple-checked it, but that's a good tip. A misspelling
or a missing space can cause an error like this I believe.

N L