Error 1004 in Excel 97...

[Guest]

A colleague uses Excel 97, and received an error "1004 application-defined or object-defined error" running a workbook that works fine in Excel 2000. Frustratingly, their version of excel doesn't permit me to edit/view the code, and the debug button is greyed out... even when i supplied a password-deprotected version
Anyway, by deduction the code stops at

Selection.Replace What:=y1, Replacement:="", LookAt:=xlWhole,
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=Fals

This sub is defined as

Option Explici
Dim y1 As Varian
Sub code(
Sheets("Sheet2").Selec
y1 = Sheets("Sheet1").Cells(1,1).Valu
Sheets("Malcoded").Columns("A:A").Selec
Selection.Replace What:=y1, Replacement:="", LookAt:=xlWhole,
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=Fals
...

Presumable something to do with how i have defined "y1", or how i hav selected the range to operate on
Any ideas... can't debug this one on their PC and no Excel 97 to test
TI

Mike

 

[Tom Ogilvy]

In Excel 97 the Replace function has these arguments:

expression.Replace(What, Replacement, LookAt, SearchOrder, MatchCase,
MatchByte)

It doesn't have a Searchformat or Replaceformat argument, so it chokes.

Since you aren't using those arguments, just remove them.

--
Regards,
Tom Ogilvy

A colleague uses Excel 97, and received an error "1004 application-defined

or object-defined error" running a workbook that works fine in Excel 2000.
Frustratingly, their version of excel doesn't permit me to edit/view the
code, and the debug button is greyed out... even when i supplied a
password-deprotected version !

Anyway, by deduction the code stops at:

Selection.Replace What:=y1, Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

This sub is defined as:

Option Explicit
Dim y1 As Variant
Sub code()
Sheets("Sheet2").Select
y1 = Sheets("Sheet1").Cells(1,1).Value
Sheets("Malcoded").Columns("A:A").Select
Selection.Replace What:=y1, Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
...

Presumable something to do with how i have defined "y1", or how i hav

selected the range to operate on.

 

[Guest]

Many thanks Tom

I cut those properies and i'll see if it works when someone can test it - i'm sure it will be fine
On a similar note - are there any good resources to easily find those properties that are supported/unsupported by various flavours of Excel - would help for these problems greatly
many thanks again

Mike

 

[Tom Ogilvy]

Not really - obviously using xl97 to check - the best approach is to develop
in the lowest version that will use the code.



--
Regards,
Tom Ogilvy

Many thanks Tom,

I cut those properies and i'll see if it works when someone can test it - i'm sure it will be fine.
On a similar note - are there any good resources to easily find those

properties that are supported/unsupported by various flavours of Excel -
would help for these problems greatly.