Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with function created in VBA
The function below excludes cells with the value of #N/A. I am also trying
to get it to exclude cells with a value of 0 (zero). Thus if I have 4 cells with the follwing values: #N/A 10 0 40 The min value that would be calculated would be 10. thanks, bob k Function MinPrice4(ParamArray arglist() As Variant) As Variant 'text or number Dim arg As Variant Dim FoundANumber As Boolean Dim myMin As Double myMin = 100 ^ 100 'some really big number FoundANumber = False For Each arg In arglist If IsNumeric(arg) Then FoundANumber = True myMin = WorksheetFunction.Min(myMin, arg) End If Next arg If FoundANumber = True Then MinPrice4 = myMin Else MinPrice4 = "Item Not Bid" End If End Function Sub aa() MsgBox MinPrice4("a", 3, CVErr(xlErrNA)) End Sub |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with function created in VBA
This works for me when I call it in a cell with =Myprice5(A1,A,2,A3,A4)
Function MinPrice5(ParamArray arglist() As Variant) As Variant 'text or number Dim arg As Variant Dim FoundANumber As Boolean Dim myMin As Double myMin = 100 ^ 100 'some really big number FoundANumber = False For Each arg In arglist If IsNumeric(arg) Then If arg 0 Then FoundANumber = True myMin = WorksheetFunction.Min(myMin, arg) End If End If Next arg If FoundANumber = True Then MinPrice5 = myMin Else MinPrice5 = "Item Not Bid" End If End Function But I prefer this since I can call it with =MinPrice4(A1:A10) Function MinPrice4(arglist) As Variant 'text or number Dim arg As Variant Dim FoundANumber As Boolean Dim myMin As Double etc -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Bob K" wrote in message ... The function below excludes cells with the value of #N/A. I am also trying to get it to exclude cells with a value of 0 (zero). Thus if I have 4 cells with the follwing values: #N/A 10 0 40 The min value that would be calculated would be 10. thanks, bob k Function MinPrice4(ParamArray arglist() As Variant) As Variant 'text or number Dim arg As Variant Dim FoundANumber As Boolean Dim myMin As Double myMin = 100 ^ 100 'some really big number FoundANumber = False For Each arg In arglist If IsNumeric(arg) Then FoundANumber = True myMin = WorksheetFunction.Min(myMin, arg) End If Next arg If FoundANumber = True Then MinPrice4 = myMin Else MinPrice4 = "Item Not Bid" End If End Function Sub aa() MsgBox MinPrice4("a", 3, CVErr(xlErrNA)) End Sub |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with function created in VBA
Bernard
thanks for your help!! "Bernard Liengme" wrote: This works for me when I call it in a cell with =Myprice5(A1,A,2,A3,A4) Function MinPrice5(ParamArray arglist() As Variant) As Variant 'text or number Dim arg As Variant Dim FoundANumber As Boolean Dim myMin As Double myMin = 100 ^ 100 'some really big number FoundANumber = False For Each arg In arglist If IsNumeric(arg) Then If arg 0 Then FoundANumber = True myMin = WorksheetFunction.Min(myMin, arg) End If End If Next arg If FoundANumber = True Then MinPrice5 = myMin Else MinPrice5 = "Item Not Bid" End If End Function But I prefer this since I can call it with =MinPrice4(A1:A10) Function MinPrice4(arglist) As Variant 'text or number Dim arg As Variant Dim FoundANumber As Boolean Dim myMin As Double etc -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Bob K" wrote in message ... The function below excludes cells with the value of #N/A. I am also trying to get it to exclude cells with a value of 0 (zero). Thus if I have 4 cells with the follwing values: #N/A 10 0 40 The min value that would be calculated would be 10. thanks, bob k Function MinPrice4(ParamArray arglist() As Variant) As Variant 'text or number Dim arg As Variant Dim FoundANumber As Boolean Dim myMin As Double myMin = 100 ^ 100 'some really big number FoundANumber = False For Each arg In arglist If IsNumeric(arg) Then FoundANumber = True myMin = WorksheetFunction.Min(myMin, arg) End If Next arg If FoundANumber = True Then MinPrice4 = myMin Else MinPrice4 = "Item Not Bid" End If End Function Sub aa() MsgBox MinPrice4("a", 3, CVErr(xlErrNA)) End Sub |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with function created in VBA
You have another response at your first(?) post.
The difference is in the way negative numbers would be treated. Bob K wrote: The function below excludes cells with the value of #N/A. I am also trying to get it to exclude cells with a value of 0 (zero). Thus if I have 4 cells with the follwing values: #N/A 10 0 40 The min value that would be calculated would be 10. thanks, bob k Function MinPrice4(ParamArray arglist() As Variant) As Variant 'text or number Dim arg As Variant Dim FoundANumber As Boolean Dim myMin As Double myMin = 100 ^ 100 'some really big number FoundANumber = False For Each arg In arglist If IsNumeric(arg) Then FoundANumber = True myMin = WorksheetFunction.Min(myMin, arg) End If Next arg If FoundANumber = True Then MinPrice4 = myMin Else MinPrice4 = "Item Not Bid" End If End Function Sub aa() MsgBox MinPrice4("a", 3, CVErr(xlErrNA)) End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
VBA | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |