Macro Run-time error '1004':

[Kody_Devl]

Hi,

I am formatting an excel spreadsheet by opening the spreadsheet in Access (2010 versions of both(Access and excel)). I have the macro code that adds sub-totals to the spreadsheet and it works perferctly but only the first time.
The second time that I run the code, it fails with a 1004 error code.

Googling I found this microsoft solution:

http://support.microsoft.com/kb/178510\

1. (pasted extract example from article)

Note The error message occurs because the code refers to the method of the cell without preceding the call with the xlSheet object variable.

2. Stop the project and change the following line of code:

Please Login or Register  to view this content.

3. Change the line of code to resemble the following line of code.

Please Login or Register  to view this content.

4. Run the program again. Notice that you can run the code multiple times without receiving an error message.

Basically it article says that you must explicitly reference the obejects as in above example:

Below I will dispay 1. the orginal recorded vba and 2. my reworking of the code to satifiy microsoft's request for explicit references. Both sets of code work the first time and both fail the second time.

Can anyone see, in my explicit reference code, what is missing?

Please Login or Register  to view this content.

my Question is:
Can anyone spot what is missing from the second line of the "code edited for explicit reference"
to complete the explicit reference requirements from the Microsoft Documention.

PS I have fixed a couple of other difference macros with 1004 error but the reference requirements were more straightforward.

Many thanks.