Excel help needed

[alrodopial]

Hi,
I want to find the minimum of a range with criteria using only VBA.

So the min of A1:A100 if all prices at this range are above 0 (>0 the criteria) with VBA
If it was a formula:
MIN(IF(A1:A100>0),A1:A100)) array formula

Any ideas?

I can loop through each cell in the range but it's a slow VBA this way

[osknows]

Hello,

how about this function in a module?

Code: Select all

Function MinExZero1(ByVal rng As Range) As Double
Dim objFunc As WorksheetFunction

Set objFunc = Application.WorksheetFunction
With objFunc
MinExZero1 = .Min(rng)
If MinExZero1 = 0 Then
MinExZero1 = .Small(rng, .CountIf(rng, "=0") + 1)
End If
End With

End Function


then call it with something such as

Code: Select all

Sub test()

a = MinExZero1(Range("a1:a4"))

End Sub


[Ian]

Don't you just need to use Application.WorksheetFunction.Min on its own ? If it returns zero then all the values in the range weren't > 0.

[osknows]

Don't you just need to use Application.WorksheetFunction.Min on its own ? If it returns zero then all the values in the range weren't > 0.

I thought the question was what is the minimum value excluding 0..?

Essentially the function uses Application.WorksheetFunction.Min on it's own and if 0 is returned then uses Application.WorksheetFunction.Small to find the next minimum above 0

[Ian]

Hi,
I want to find the minimum of a range with criteria using only VBA.

So the min of A1:A100 if all prices at this range are above 0 (>0 the criteria) with VBA
If it was a formula:
MIN(IF(A1:A100>0),A1:A100)) array formula

Any ideas?

I can loop through each cell in the range but it's a slow VBA this way

All the prices in the range are > 0 is how I read it.

[osknows]

Hi,
I want to find the minimum of a range with criteria

I read this bit..

[Ian]

Is there a reason why you couldn't have used ....SMALL on its own ?

I've got that formula in a couple of spreadsheets (not vba though) and it
always finds the minimum OK even if none of the cells pass the criteria as you end up with 1 for the n-th position.

[osknows]

I suppose for what it's being used for then using SMALL is fine. The calculation time for SUMIF and COUNTIF are proportionate to the number of cells covered, so over large ranges the MIN function is quicker, hence why it's used first.

[alrodopial]

Thanks os, I'll give it a try later tonight.
Ian you are right, the way I wrote it wasn't clear enough (English ....)
I meant the smallest of the range and at the same time larger than zero.

Thanks guys

[alrodopial]

It's working fine,
Thanks