Unable to get the Min property of the WorksheetFunction clas

[miconian]

Here's my 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...

 

[vconfused]

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,"")?

 

[Scott Huish]

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

 

[miconian]

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?

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

 

[Scott Huish]

Re: Unable to get the Min property of the WorksheetFunction

Perhaps:

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

 

[miconian]

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.

 

[miconian]

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...

 

[miconian]

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"?

 

[Scott Huish]

Re: Unable to get the Min property of the WorksheetFunction

Yes.

 

[miconian]

Re: Unable to get the Min property of the WorksheetFunction

Thanks again...