Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 3rd 08, 02:36 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
Posts: 133
Default 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   Report Post  
Old January 3rd 08, 03:45 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,101
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Question about a new equation or function I am unfimiliar with Rodney Watters Excel Discussion (Misc queries) 3 November 7th 08 02:21 PM
equation / function question [email protected] Excel Worksheet Functions 1 January 24th 07 04:30 AM
Excel "Insert Formula" dialog always call my user defined function [email protected][_2_] Excel Programming 0 March 1st 06 02:35 AM
User define function and dialog [email protected][_2_] Excel Programming 0 February 15th 06 05:54 PM
User function argument question Richard Ahlvin Excel Programming 4 September 22nd 05 02:40 PM


All times are GMT +1. The time now is 02:10 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017