Results 1 to 10 of 10

Unable to get the Min property of the WorksheetFunction clas

This is a discussion on Unable to get the Min property of the WorksheetFunction clas within the Excel Questions forums, part of the Question Forums category; Here's my code: Code: If Target.Column = Range("E:E").Column Then lblOverallFltStart.Caption = "" & Format(Application.WorksheetFunction.Min(Range("O:O")), "mm/dd/yy") End If If Target.Column = ...

  1. #1
    Board Regular
    Join Date
    Aug 2004
    Posts
    759

    Default Unable to get the Min property of the WorksheetFunction clas

    Here's my code:

    Code:
    If Target.Column = Range("E:E").Column Then
    lblOverallFltStart.Caption = "" & Format(Application.WorksheetFunction.Min(Range("O:O")), "mm/dd/yy")
    End If
    If Target.Column = Range("E:E").Column Then
    lblOverallFltEnd.Caption = "" & Format(Application.WorksheetFunction.Max(Range("P:P")), "mm/dd/yy")
    End If
    And the error: Unable to get the Min property of the WorksheetFunction class"

    Now, here's the weird thing. I did not have this problem yesterday. I admit that a lot has happened to the worksheet in the itnerim, but I'm pretty sure the WorksheetFunction class had a Min property before I went to sleep last night.

    ...so maybe the idea is that the property exists, but it's not working? Here's the only factor I can think of. The column that I am trying to get the minimum date for contains formulas (to create that date based on data in other cells). Ergo, some of the cells in columns O and P have formulas in them which are currently yielding "#VALUE!" because they have no data to draw from. Is this the problem? And, er, what should I do about it?

    Thanks...

  2. #2
    Board Regular
    Join Date
    Jun 2004
    Location
    Philadelphia, PA
    Posts
    542

    Default Re: Unable to get the Min property of the WorksheetFunction

    Quite possibly it is the problem. Try using the Application.WorksheetFunction.Min() on a range without any errors, it should work fine. Can you modify your functions to return nothing rather than #Value errors? Perhpas using somthing like =if(iserror(func),func,"")?

  3. #3
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    16,846

    Default Re: Unable to get the Min property of the WorksheetFunction

    That is the problem. Fixing your formulas to get rid of the errors would probably be the easiest route, and to help you with your code a little bit:

    If Target.Column = 5 Then
    lblOverallFltStart.Caption = "" & Format(Application.WorksheetFunction.Min(Range("O:O")), "mm/dd/yy")
    lblOverallFltEnd.Caption = "" & Format(Application.WorksheetFunction.Max(Range("P:P")), "mm/dd/yy")
    End If

  4. #4
    Board Regular
    Join Date
    Aug 2004
    Posts
    759

    Default Re: Unable to get the Min property of the WorksheetFunction

    I dunno. Here's one of the formulas. How would you modify it to return nothing instead of an error if some of the cells feeding into it were blank?

    Code:
    =DATE($G9,$E9,LEFT($F9,FIND("-",$F9)-1))

  5. #5
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    16,846

    Default Re: Unable to get the Min property of the WorksheetFunction

    Perhaps:

    =IF(COUNTA(E9:G9)=3,DATE($G9,$E9,LEFT($F9,FIND("-",$F9)-1)),"")

  6. #6
    Board Regular
    Join Date
    Aug 2004
    Posts
    759

    Default Re: Unable to get the Min property of the WorksheetFunction

    Hotpepper: I don't think there's anything wrong with the formulas. They just have references in some cases to cells that are blank. But I don't know what to do about that. Those cells are going to get filled in someday, and the formulas need to be waiting.

    Thanks for the help with cleaning up the code, though.

  7. #7
    Board Regular
    Join Date
    Aug 2004
    Posts
    759

    Default Re: Unable to get the Min property of the WorksheetFunction

    ack. I'm one step behind you in posting. Just a minute while I try that...:)

  8. #8
    Board Regular
    Join Date
    Aug 2004
    Posts
    759

    Default Re: Unable to get the Min property of the WorksheetFunction

    Wow. That works. I am really amazed.

    Does IF(COUNTA(E9:G9)=3 basically say "If E9,F9, and G9 all contain something"?

  9. #9
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    16,846

    Default Re: Unable to get the Min property of the WorksheetFunction

    Yes.

  10. #10
    Board Regular
    Join Date
    Aug 2004
    Posts
    759

    Default Re: Unable to get the Min property of the WorksheetFunction

    Thanks again...

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