Remember Me?

#1
January 3rd 08, 03:36 PM posted to microsoft.public.excel.programming
 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

#2
January 3rd 08, 04:45 PM posted to microsoft.public.excel.programming
 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

 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 03:21 PM [email protected] Excel Worksheet Functions 1 January 24th 07 05:30 AM [email protected][_2_] Excel Programming 0 March 1st 06 03:35 AM [email protected][_2_] Excel Programming 0 February 15th 06 06:54 PM Richard Ahlvin Excel Programming 4 September 22nd 05 02:40 PM

All times are GMT +1. The time now is 08:12 PM.