runtime error "unable to get Max property of worksheetfuncti

[ecupstid]

I have the following VB code in Excel 2000 and it has been working perfectly for a year, now I am getting the error, ANY HELP WOULD BE APPRECIATED:

Sub Autpen()
'
' Macro recorded 1/17/2002
'
ChDir "f:"
Workbooks.OpenText FileName:="f:generic.exc", Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _
, Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 2 _
), Array(2, 2), Array(3, 2), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), _
Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array( _
28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1))

Dim Count As Long
Dim i As Long
Dim SheetName As String
Dim LastRow As Long
Dim LastColumn As Long


SheetName = ActiveSheet.Name
LastRow = Range("A1").End(xlDown).Row
LastColumn = Range("A1").End(xlToRight).Column
Range("A1").End(xlToRight).Offset(0, 1).Formula = "Header"
Range("A1").End(xlToRight).Offset(1, 0).Formula = "1"
Range(Range("A1").End(xlToRight).Offset(2, 0).Address, _
Range("A1").End(xlDown).End(xlToRight). _
Address).Formula _
= "=IF(RC[-" & LastColumn - 1 & "]=R[-1]C[-" & _
LastColumn - 1 & "],R[-1]C,R[-1]C+1)"

GETTING A RUN-TIME ERROR 1004 UNABLE TO GET THE MAX PROPERTY OF THE WORKSHEETFUNCTION CLASS HERE
Count = Application.WorksheetFunction.Max( _
Range(Range("B1").End(xlToRight).Offset(2, 0).Address, _
Range("B1").End(xlDown).End(xlToRight).Address))

i = 1
Do Until i > Count
Range(Cells(1, 1), Cells(LastRow, (LastColumn + 1))).Select
Selection.AutoFilter Field:=Range("A1").End(xlToRight).Column, _
Criteria1:=i
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets.Add after:=ActiveSheet
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Select
Range("A1").End(xlToRight).EntireColumn.ClearContents
Sheets(SheetName).Select
i = i + 1
Loop
Application.DisplayAlerts = False
Sheets(SheetName).Delete
Application.DisplayAlerts = True
For i = 1 To Sheets.Count
Sheets(i).Select
ActiveSheet.Name = Format(Range("B2").Value, "mm-dd-yyyy")
Rows("1:1").RowHeight = 65
Range("A1:AG1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Selection.Font.Bold = True
Columns("A:A").ColumnWidth = 11.43
Columns("B:B").ColumnWidth = 10
Columns("C:C").ColumnWidth = 9.71
Columns("D:D").ColumnWidth = 9.71
Columns("E:E").ColumnWidth = 9.57
Columns("F:F").ColumnWidth = 11.29
Columns("G:G").ColumnWidth = 11.29
Columns("D:H").Select
Selection.NumberFormat = "0.00"
Columns("I:I").ColumnWidth = 12.29
Columns("J:J").ColumnWidth = 25.29
Columns("K:K").ColumnWidth = 24
Columns("M:M").ColumnWidth = 10
Columns("R:R").ColumnWidth = 9.14
Columns("U:U").ColumnWidth = 12
Columns("V:V").ColumnWidth = 12
Columns("Z:AG").Select
Selection.NumberFormat = "0.00"
Columns("Z:Z").ColumnWidth = 9.43
Columns("AF:AF").ColumnWidth = 11.86
Columns("AG:AG").ColumnWidth = 10.43
Columns("AG:AG").Select
Selection.NumberFormat = "#,##0.00"
Range("U2:V2", Range("U2:V2").End(xlDown)).Select
With Selection.Interior
.ColorIndex = 24
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("L2:M2", Range("L2:M2").End(xlDown)).Select
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("Q2", Range("Q2").End(xlDown)).Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("R2", Range("R2").End(xlDown)).Select
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("N2", Range("N2").End(xlDown)).Select
With Selection.Interior
.ColorIndex = 19
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Selection.Font.Bold = True
Range("Z2", Range("Z2").End(xlDown)).Select
With Selection.Interior
.ColorIndex = 19
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Selection.Font.Bold = True
Rows(2).Select
ActiveWindow.FreezePanes = True

Range("A1").Select


Next i

Sheets(1).Select
Range("A2").Select
ChDir "f:"
ThisFile = "f:" + Range("A2").Value
ActiveWorkbook.SaveAs FileName:=ThisFile, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWorkbook.RunAutoMacros Which:=xlAutoClose

Dim Wb As Workbook

For Each Wb In Workbooks
If Wb.Name <> ThisWorkbook.Name Then
Wb.Close savechanges:=True
End If
Next Wb
Application.Quit

End Sub

 

[parry]

Hi sorry no answer Im afraid but this link to the MSKB has a list of errors and a description, including 1004. Oddly I cant see this error listed in the Excel VBA Help.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;142138

 

[ecupstid]

Thanks for the information, I will take a look and see if I can find a solution there. It is strange that this has been working for a year without any problems.

Regards,
Ed

 

[Andrew Poulsom]

You would get that error if the result of the MAX function was an error eg #N/A or #VALUE!. Maybe one or more of the formulas you are entering in your code results in an error.

 

[ecupstid]

I appreciate your reply and will review the data/formulas for the values you pointed out.

Thanks,
Ed