How to exclude value from Application.Min

zico8

Board Regular
Joined
Jul 13, 2015
Messages
227
Hi,

How should I change the 6th line in below code If I want the result D is 8 ?
Code:
Sub nulltest()
A = 4
B = 8
C = 20
If A < 10 Then
    A = Null
End If
D = Application.Min(A, B, C)
End Sub
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
One way would be to make A some very large number that you would never use, like this:
Code:
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Last edited:
Upvote 0
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):
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
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top