Remember Me?

 SteveM external usenet poster First recorded activity by ExcelBanter: Jan 2008 Posts: 133 User Function Question: Collect Condition in Dialog Box - But How toInsert into Function Equation?

Excel Genii,

I've Created MaxIf and MinIf functions analogous to SumIf. But it's
not clear to me how to insert the Condition string captured in the
dialog box into the IF block in the functions themselves in order to
check each value in the selected range.

Any ideas?

Thanks,

Steve

 joel external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 9,101 User Function Question: Collect Condition in Dialog Box - But How

Below is a simple form of the function. It doesn't cover the case where the
criteria may be a range of cells, but does show how to break the condition
into multiple parts.

Function maxif(Target As Range, criteria As String)

If InStr(criteria, "<") 0 Then
Value1 = Left(criteria, InStr(criteria, "<") - 1)
If InStr(criteria, "=") 0 Then
Condition = "<="
Value2 = Mid(criteria, InStr(criteria, "=") + 1)
Else
Condition = "<"
Value2 = Mid(criteria, InStr(criteria, "<") + 1)
End If
Else
If InStr(criteria, "") 0 Then
Value1 = Left(criteria, InStr(criteria, "") - 1)
If InStr(criteria, "=") 0 Then
Condition = "="
Value2 = Mid(criteria, InStr(criteria, "=") + 1)
Else
Condition = ""
Value2 = Mid(criteria, InStr(criteria, "") + 1)
End If
Else
'must be just an equal sign
Value1 = Left(criteria, InStr(criteria, "=") - 1)
Value2 = Mid(criteria, InStr(criteria, "=") + 1)
Condition = ""
End If

If Not IsNumeric(Value1) Then
Value1 = Range(Value1)
End If
If Not IsNumeric(Value2) Then
Value2 = Range(Value2)
End If

Select Case Condition
Case ""
Truth = Value1 Value2
Case "="
Truth = Value1 = Value2
Case "<"
Truth = Value1 < Value2
Case "<="
Truth = Value1 <= Value2
Case "="
Truth = (Value1 = Value2)
End Select

End Function

"SteveM" wrote:

Excel Genii,

I've Created MaxIf and MinIf functions analogous to SumIf. But it's
not clear to me how to insert the Condition string captured in the
dialog box into the IF block in the functions themselves in order to
check each value in the selected range.

Any ideas?

Thanks,

Steve

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Rodney Watters Excel Discussion (Misc queries) 3 November 7th 08 02:21 PM [email protected] Excel Worksheet Functions 1 January 24th 07 04:30 AM [email protected][_2_] Excel Programming 0 March 1st 06 02:35 AM [email protected][_2_] Excel Programming 0 February 15th 06 05:54 PM Richard Ahlvin Excel Programming 4 September 22nd 05 02:40 PM

All times are GMT +1. The time now is 02:10 AM. Copyright ©2004-2019 ExcelBanter.