Results 1 to 5 of 5

runtime error "unable to get Max property of worksheetfuncti

This is a discussion on runtime error "unable to get Max property of worksheetfuncti within the Excel Questions forums, part of the Question Forums category; I have the following VB code in Excel 2000 and it has been working perfectly for a year, now I ...

  1. #1
    New Member
    Join Date
    Dec 2002
    Posts
    31

    Default

    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 Auto_Open()
    '
    ' 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





  2. #2
    MrExcel MVP parry's Avatar
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    3,355

    Default

    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...b;EN-US;142138

  3. #3
    New Member
    Join Date
    Dec 2002
    Posts
    31

    Default

    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

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    64,517

    Default

    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.

  5. #5
    New Member
    Join Date
    Dec 2002
    Posts
    31

    Default

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

    Thanks,
    Ed

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com