Solved: Code Runs on Excel 2000 but not on Excel 2002

[Opv]

Can someone please tell me why the code below runs flawlessly on Excel 2000 and causes an error on Excel 2002, running on Windows XP, Service Pack3? What needs to be changed to make it work on any version of Excel?

Opv


[VBA]
Sub Auto_Open()


' Auto_Open Macro
' Macro recorded 2/12/2010 by John Q. Doe
'

MyRows = Range("Date").Cells.SpecialCells(xlCellTypeBlanks).Count

If MyRows <= 3 Then
Call InsertRow
Application.CutCopyMode = False
End If

rptMonth = Range("ReportMonth").Value
forMonth = Application.Max([B:B])
If rptMonth <> forMonth Then
Range("ReportMonth").ClearContents
Range("ReportMonth").Value = forMonth
End If

End Sub

[/VBA]

[GTO]

Hi there,

Maybe I'm glossing over something, but I don't see anything to go kathunk. Are you sure it's not erring someplace in 'InsertRow()'?

Again, maybe I'm just not awake yet, but presuming that Col B has dates and "ReportMonth" refers to a one-cell range, could you shorten the above to:

    MyRows = Range("Date").Cells.SpecialCells(xlCellTypeBlanks).Count
     
    If MyRows <= 3 Then
        Call InsertRow
        Application.CutCopyMode = False
    End If
     
    Range("ReportMonth").Value = Application.Max([B:B])

If you post back with a small example wb, I'd probably get a chance to test later (at work w/2003; I'm signing off, but currently in 2000, so didn't test)

Mark

[Opv]

The shortened code works great! Thanks for the suggestion. On the code, it turns out that my brother had done something to corrupt the sheet itself. The problem was not in the code. I sent him an clean version of the worksheet and it is working fine. Thanks again!

[GTO]

Well, I really didn't do anything, but am sure glad its working :-)

By the way, hopefully not an unwanted point, but I forgot to mention that using Evaluate in this manner is slower than Range.

Have a great evening,

Mark