Excel 2002 and 97 compatability

[Andy]

I have written a simple bit of code that works perfectly on my machine
(PC with XP and Excel 2002). When I send it to a friend with Excel 97
he gets a 'runtime 13 error', presumably because he has an older version
of Excel.

Is there anyway of finding out what is causing the problem so that I can
re-code the VBA macro another way?

The code is simply:

Sub Macro1()
For Each blankcell In Range("M2:M10000")
If blankcell.Value <> 0 Then
blankcell.Offset(0, 3).Activate
Selection.Resize(1, 38).Select
Selection.SpecialCells(xlCellTypeConstants,2).Select
Selection.Copy
Selection.End(xlToRight).Select
Selection.Offset(0, 1).Activate
ActiveSheet.Paste
Application.CutCopyMode = False
End If
Next blankcell
Columns("P:BB").Select
Selection.EntireColumn.Hidden = True
Range("A2").Select
End Sub



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Tom Ogilvy]

This line of code will cause that problem if blankcell does not contain a
number:

If blankcell.Value <> 0 Then

Sub Macro1()
For Each blankcell In Range("M2:M10000")
if blankCell <> "" and isnumeric(blankcell) then
If blankcell.Value <> 0 Then
blankcell.Offset(0, 3).Activate
Selection.Resize(1, 38).Select
Selection.SpecialCells(xlCellTypeConstants,2).Select
Selection.Copy
Selection.End(xlToRight).Select
Selection.Offset(0, 1).Activate
ActiveSheet.Paste
Application.CutCopyMode = False
End If
End if
Next blankcell
Columns("P:BB").Select
Selection.EntireColumn.Hidden = True
Range("A2").Select
End Sub

Should work. Guessing and the conditions you want to impose, so check the
logic - but you want to avoid doing a numeric comparison unless the cell
contains a number. Just as a warning, you can not do something like this

If isnumeric(blankcell) and blankcell <> 0 then

because the line is fully evaluated and you would still get the error on the
second condition.

This is caused by xl97.

Regards,
Tom Ogilvy

 

[steve]

Excel 97 does not recognize "blankcell". It needs to be defined.
Recommend
Dim blankcell as Range

Also this line will error out if there is nothing there,

Selection.SpecialCells(xlCellTypeConstants,2).Select

recommend an error check.

steve