Runtime Error Code 1004

[laks]

Hi

I am converting Excel Templates which were developed in office 97 to Excel 2003.The templates are running fine in office 97 ,but when I run the same template in off 2003 I get the error "Run time Error Code 1004 : Application or Object Defined " and when I tried to debug the cursor is pointing to a cell which contains a formula which add multiple cell values.Can any one throw some light on this why the template which works fine on office 97 is not working in office 2003?.

Quick help is appreciated

Thanks & Regards

Laks

[Bryan Hessey]

Hi

I am converting Excel Templates which were developed in office 97 to Excel 2003.The templates are running fine in office 97 ,but when I run the same template in off 2003 I get the error "Run time Error Code 1004 : Application or Object Defined " and when I tried to debug the cursor is pointing to a cell which contains a formula which add multiple cell values.Can any one throw some light on this why the template which works fine on office 97 is not working in office 2003?.

Quick help is appreciated

Thanks & Regards

Laks

Hi,

The error 1004 is telling you that you cannot do what you are trying to do in the place that you are trying to do it, and the cursor position is not always useful.

Check your assumed access to objects, and define that access where required (ie, for Range(~~ try activesheet.Range(~~ etc), this is a time when the step-by-step is really useful.

hth
---

[laks]

Hi

Thanks for the quick reply.But since my company has upgraded from office 97 to office 2003 I am in a position to convert my entire templates in Excel 2003.
Since there is no flow change in the templates I am just opening it in Excel 2003 and trying to run it to make sure there is no issue due running them in office 2003.There is no single line of code as an addition.

The programme which I am trying to convert contains lot of process but ultimately it does summarisation of the data given as input and gives the output in a presentation format.

[Bryan Hessey]

Hi

Thanks for the quick reply.But since my company has upgraded from office 97 to office 2003 I am in a position to convert my entire templates in Excel 2003.
Since there is no flow change in the templates I am just opening it in Excel 2003 and trying to run it to make sure there is no issue due running them in office 2003.There is no single line of code as an addition.

The programme which I am trying to convert contains lot of process but ultimately it does summarisation of the data given as input and gives the output in a presentation format.

Yes, but not all the code that worked in '97 seems to run well in 2003, you will need to track down any offending lines (and no, I have no idea which they might be, but assumptions as to ActiveSheet seem to be one cause).

The CTRL/F8 (run to cursor) should be helpful to you here.

---

[laks]

Hi

One quick doubt ,is there any limit for the cell holding the reference of the other cells in 2003 ?.Because in my case the cell is referring sum of around 130 cell values.

Quick help is appreciated

Thanks

Laks

[Bryan Hessey]

Hi

One quick doubt ,is there any limit for the cell holding the reference of the other cells in 2003 ?.Because in my case the cell is referring sum of around 130 cell values.

Quick help is appreciated

Thanks

Laks

the limit is 32768 data characters per cell, formula is (from memory) 1024 characters cell formula

---

[laks]

Hi

One quick update needed ,i tried to put On error resume next in the function where it was giving error and it executed fine till the end?Is it throwing some kind of clue.I compared the results also ,it looks same thou i didnt check in detail.


Thanks & Regards

Laks

[Bryan Hessey]

Hi

One quick update needed ,i tried to put On error resume next in the function where it was giving error and it executed fine till the end?Is it throwing some kind of clue.I compared the results also ,it looks same thou i didnt check in detail.


Thanks & Regards

Laks

Hi,

the 'on error resume next' simply says 'ignore errors and go ahead' - a dangerous ploy unless you are absolutely sure you can ignore the error.

It would normally be used for something like create a (possibly already existing) file, where you would 'ignore error, create file, reset error condition'.

If the instruction was 'Sheet3.select', scribble on .selection etc then your 'ignore error' could prove to be a problem.

As the 1004 error says (basically) that "you can't do that there" then ignoring it is not what I would call 'wise'

You can use the Spot-Stop (correct name unknown) to progress through your code to locate the area, and then the line, in which the error occurs.
(ie, spot-stop every 10th line, then, when it errors, spot-stop every line in that 10, the line that you were last 'yellow' on is the offending code)

Hope this helps.
---

[laks]

Hi
Thanks for the reply.The same code I did debug in office 97 and the same line which wasthrowing in office 2003 went ahead without throwing any errors .Also when I was getting error I tried to change the formula which was throwing error by removing some cells references (it was actually sum of 130 columns,I removed few cell reference from the formula ),it went ahead with out any errors.So any clue on this what can be done for this ?


Thanks & Regards

Laks

[Bryan Hessey]

Hi
Thanks for the reply.The same code I did debug in office 97 and the same line which wasthrowing in office 2003 went ahead without throwing any errors .Also when I was getting error I tried to change the formula which was throwing error by removing some cells references (it was actually sum of 130 columns,I removed few cell reference from the formula ),it went ahead with out any errors.So any clue on this what can be done for this ?


Thanks & Regards

Laks

Hi,

with no view of the formula it would be just a guess on anyones part, I'll presume the sum was expressed as a range rather than as an overlong line of +'s

I am not aware of any such limitation in 2003, but if you keep the sum as two part-statements with no further problems it might be worth further investigation.

---

[laks]

Hi

Thanks for the reply.Unforunately the formula is an overlong line of +'s.I am just pastng the formlula here may be it will give some clue.The formula is



($AD$7+$AD$10+$AD$13+$AD$16+$AD$19+$AD$22+$AD$25+$AD$28+$AD$31+$AD$34+$AD$37+$AD$40+$AD$43+$AD$46+$AD$49+$AD$52+$AD$55+$AD$58+$AD$61+$AD$64+$AD$67+$AD$70+$AD$73+$AD$76+$AD$79+$AD$82+$AD$85+$AD$88+$AD$91+$AD$94+$AD$97+$AD$100+$AD$103+$AD$106+$AD$109+$AD$112+$AD$115+$AD$118+$AD$121+$AD$124+$AD$127+$AD$130+$AD$133+$AD$136+$AD$139+$AD$142+$AD$145+$AD$148+$AD$151+$AD$154+$AD$157+$AD$160+$AD$163+$AD$166+$AD$169+$AD$172+$AD$175+$AD$178+$AD$181+$AD$184+$AD$187+$AD$190+$AD$193+$AD$196+$AD$199+$AD$205+$AD$208+$AD$211+$AD$214+$AD$217+$AD$220+$AD$223+$AD$226+$AD$229+$AD$232+$AD$235+$AD$238+$AD$241+$AD$244+$AD$247+$AD$250+$AD$253+$AD$256+$AD$259+$AD$262+$AD$265+$AD$268+$AD$271+$AD$274+$AD$277+$AD$280+$AD$283+$AD$286+$AD$289+$AD$292+$AD$295+$AD$298+$AD$301+$AD$304+$AD$307+$AD$310+$AD$313+$AD$316+$AD$319+$AD$322+$AD$325+$AD$328+$AD$331+$AD$334+$AD$337+$AD$340+$AD$343+$AD$346+$AD$349+$AD$352+$AD$355+$AD$358+$AD$361+$AD$364+$AD$367+$AD$370+$AD$373+$AD$376+$AD$379+$AD$382+$AD$385+$AD$388+$AD$391+$AD$394+$AD$397)


Regrads
Laks

[Bryan Hessey]

Hi

Thanks for the reply.Unforunately the formula is an overlong line of +'s.I am just pastng the formlula here may be it will give some clue.The formula is



($AD$7+$AD$10+~~+$AD$397)


Regrads
Laks

Hi,

that Excel 2003 has a formula length maximum of 1024 characters, and your display is 1029 characters, would suggest that splitting this into two instructions is the way to go.

hth
---