Hi,

I coded a VBA macro in an Excel 97 workbook that imports
data from several diferent excel 97 workbooks into the one
master book (master book contains the macro).

We are now in the process of migrating to Office XP. When
the macro is run in XP, I recieve the following error:

"Runtime Error 1004 -- Application Defined or Object
Defined Error"

The macro ran error free for several months in 97, so I am
confident that this error is due to the change in version.

Any suggestions or ideas on what could be causing this
error?

Thanks.

John.

Hi,

Sorry for the delay, this obviously would have helped.
When stepping through the application the error appears
when the following line of code is executed:

ThisWorkbook.sheets(SheetName).Cells(Row, Col) = value

John.

-----Original Message-----
When you get the error, what is the line of code that is
highlighted?

You should begin by looking at the line.

--
Regards,
Tom Ogilvy

"John" wrote in message
...
Hi,

I coded a VBA macro in an Excel 97 workbook that imports
data from several diferent excel 97 workbooks into the
one
master book (master book contains the macro).

We are now in the process of migrating to Office XP.
When
the macro is run in XP, I recieve the following error:

"Runtime Error 1004 -- Application Defined or Object
Defined Error"

The macro ran error free for several months in 97, so I
am
confident that this error is due to the change in
version.

Any suggestions or ideas on what could be causing this
error?

Thanks.

John.


.

if sheetname didn't refer to a valid sheet, you would get a subscript out of
range, so I think we can assume that isn't the source

Therefore the Row or Col argument seems likely.

Sub Tester1()
Value = 10
Row = 0
col = 6
sheetname = "Sheet1"
ThisWorkbook.Sheets(sheetname).Cells(Row, col) = Value
End Sub

gives me that error - a value of zero for row is not legal.

Sub Tester1()
Value = 10
Row = 10
col = 0
sheetname = "Sheet1"
ThisWorkbook.Sheets(sheetname).Cells(Row, col) = Value
End Sub

gives me the error - col = 0 is not legal.

Perhaps put this before the offending line

msgbox "row: " & row & " col: " & col

It is possible that using row can be problematic in xl2002, but I doubt it.
More than likely, something has changed so that either row or col is not
getting proper value. Since these are variable, there must be some code
that are assigning them a value.

--
Regards,
Tom Ogilvy


John wrote in message
...
Hi,

Sorry for the delay, this obviously would have helped.
When stepping through the application the error appears
when the following line of code is executed:

ThisWorkbook.sheets(SheetName).Cells(Row, Col) = value

John.

-----Original Message-----
When you get the error, what is the line of code that is
highlighted?

You should begin by looking at the line.

--
Regards,
Tom Ogilvy

"John" wrote in message
...
Hi,

I coded a VBA macro in an Excel 97 workbook that imports
data from several diferent excel 97 workbooks into the
one
master book (master book contains the macro).

We are now in the process of migrating to Office XP.
When
the macro is run in XP, I recieve the following error:

"Runtime Error 1004 -- Application Defined or Object
Defined Error"

The macro ran error free for several months in 97, so I
am
confident that this error is due to the change in
version.

Any suggestions or ideas on what could be causing this
error?

Thanks.

John.


.

I would agree, row and col would seem to be the obvious
source of the issue, however this works and has worked
fine in Excel 97 for months.

I'm tempted to re-code the problem area in XP and see if
it works. Unfortuneatley I had no control over the now-
owner of the app migrating to XP from 97. Thats a large
version gap, and I knew there would be issues...

Thanks for the input.
John

-----Original Message-----
if sheetname didn't refer to a valid sheet, you would get
a subscript out of
range, so I think we can assume that isn't the source

Therefore the Row or Col argument seems likely.

Sub Tester1()
Value = 10
Row = 0
col = 6
sheetname = "Sheet1"
ThisWorkbook.Sheets(sheetname).Cells(Row, col) = Value
End Sub

gives me that error - a value of zero for row is not
legal.

Sub Tester1()
Value = 10
Row = 10
col = 0
sheetname = "Sheet1"
ThisWorkbook.Sheets(sheetname).Cells(Row, col) = Value
End Sub

gives me the error - col = 0 is not legal.

Perhaps put this before the offending line

msgbox "row: " & row & " col: " & col

It is possible that using row can be problematic in
xl2002, but I doubt it.
More than likely, something has changed so that either
row or col is not
getting proper value. Since these are variable, there
must be some code
that are assigning them a value.

--
Regards,
Tom Ogilvy


John wrote in message
...
Hi,

Sorry for the delay, this obviously would have helped.
When stepping through the application the error appears
when the following line of code is executed:

ThisWorkbook.sheets(SheetName).Cells(Row, Col) = value

John.

-----Original Message-----
When you get the error, what is the line of code that
is
highlighted?

You should begin by looking at the line.

--
Regards,
Tom Ogilvy

"John" wrote in message
...
Hi,

I coded a VBA macro in an Excel 97 workbook that
imports
data from several diferent excel 97 workbooks into
the
one
master book (master book contains the macro).

We are now in the process of migrating to Office XP.
When
the macro is run in XP, I recieve the following
error:

"Runtime Error 1004 -- Application Defined or Object
Defined Error"

The macro ran error free for several months in 97,
so I
am
confident that this error is due to the change in
version.

Any suggestions or ideas on what could be causing
this
error?

Thanks.

John.


.



.