How to exclude value from Application.Min

[zico8]

Hi,

How should I change the 6th line in below code If I want the result D is 8 ?

Sub nulltest()
A = 4
B = 8
C = 20
If A < 10 Then
    A = Null
End If
D = Application.Min(A, B, C)
End Sub

 

[Joe4]

One way would be to make A some very large number that you would never use, like this:

Sub nulltest()
A = 4
B = 8
C = 20
If A < 10 Then
[COLOR=#ff0000]    A = 999999999[/COLOR]
End If
D = Application.Min(A, B, C)
End Sub

 

[zico8]

It is not an answer I am looking for

 

[Joe4]

It is not an answer I am looking for

It returns 8. Is that not what you are after?
If not, you will need to explain in more detail.
Perhaps you should create an array, and add all the value that meet your criteria to the array, and then take the minimum of that.

 

[zico8]

Hi,
Right, the result should be 8, but I do not want to assign from hand any value to my variable cause of I cannot be sure what the real value will be (it can be also 999999).
I was rather thinking about assigning any non numeric value, but Min function converts it to 0

I suppose that using array could solve the issue. Can you help me with that?

 

[Joe4]

I get the feeling that you have oversimplified a bigger issue/problem. It is important for us to get a full understanding to provide you with the best solution.

Where will these values be coming from (how will they be entered/loaded)?
The fact that you say that you are not sure what they will be (i.e. they may change) leads me to believe that they won't be hard-coded into your VBA code.

And how are you determining which values to exclude from this calculation?

 

[Joe4]

OK, here is an example using arrarys that is very close to your original example. The only difference is I have the values 4,8,20 coming from the range A1:C1 (instead of being hard-coded into the VBA code):

Sub Test()

    Dim D As Double
    Dim rng As Range
    Dim cell As Range
    Dim counter As Long
    Dim arr()
    Dim i As Long
    ReDim arr(0)
    
'   Set range where data is stored
    Set rng = Range("A1:C1")
    
'   Loop through all cells in range
    For Each cell In rng
'       Check value of first record to see if to exclude it
        If (counter = 0) And cell.Value < 10 Then
'           Do nothing
        Else
'           Add to array
            arr(i) = cell
            i = i + 1
            ReDim Preserve arr(i)
        End If
'       Increment counter
        counter = counter + 1
    Next cell
    
'   Return minimum value from array
    D = Application.WorksheetFunction.Min(arr)
    MsgBox D
    
End Sub