Runtime Error 1004 -- Application Defined or Object Defined Error

[John]

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.

 

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

 

[Tom Ogilvy]

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.

 

[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