Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 on VBA operation
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 on VBA operation
Make sure the worksheet name is spelled exactly the same as the macro.
"N L" 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. Thanks, N L |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 on VBA operation
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... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 on VBA operation
On Mar 20, 5:21 am, N L 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. 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 on VBA operation
On Mar 19, 6:50*pm, "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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 on VBA operation
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 on VBA operation
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 on VBA operation
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. 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! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 on VBA operation
On Mar 20, 8:26*pm, "IanKR" wrote:
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 on VBA operation
On Mar 19, 2:51*pm, Joel wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Large Operation Error | Excel Discussion (Misc queries) | |||
Change cut/paste operation to cut/insert operation | Excel Programming | |||
Getting an error message that reads "illegal operation....VBE6.DL. | Excel Discussion (Misc queries) | |||
why do I get a illegal operation error? | New Users to Excel |