Finding lowest non zero value in excel VBA

[raj]

How can i get the lowest non zero value among a set of integers within excel VBA ?

 

[Rick Rothstein]

Sub Test()
  MsgBox Evaluate("MIN(" & Replace(Application.Trim(Replace(" " & Join(Evaluate("TRANSPOSE(A1:A100)")) & " ", " 0 ", " ")), " ", ",") & ")")
End Sub

very good short and precise solution

Did you see the code Fluff posted in Message #7 yet? And if XL2019 has the MINIFS function, Peter's code in Message #8?

 

[purinqui]

For typed characters, yes but for processes/time no.

This has a few more characters but nearly twice as fast for me.

Sub Test5()
  MsgBox = Evaluate("minifs(a1:a100,a1:a100,""<>0"")")
End Sub

An small change needed the sentence to work :
The parenthesis between the MIN and the IF was missing and the letter "s" had to be removed after the IF

This code its working properly:

Evaluate("min(if(d33:m33,d33:m33,""<>0""))")

 

[purinqui]

Did you see the code Fluff posted in Message #7 yet?

Code in Message #7 its working properly in XL2019:

Sub Test4()
MsgBox [min(if(a1:a100<>0,a1:a100))]
End Sub

And if XL2019 has the MINIFS function, Peter's code in Message #8?

Code in Message #8, It was not working :

Sub Test5()
MsgBox = Evaluate("minifs(a1:a100,a1:a100,""<>0"")")
End Sub

An small change needed the sentence to work :
The parenthesis between the MIN and the IF was missing and the letter "s" had to be removed after the IF
And now, the code works properly in XL2019

Evaluate("min(if(a1:a100,a1:a100,""<>0""))")

 

[Peter_SSs]

An small change needed the sentence to work :
The parenthesis between the MIN and the IF was missing and the letter "s" had to be removed after the IF

It shouldn't need that change as MINIFS is a valid function in Excel 2019 according to Microsoft's help on this function.
The code certainly worked for me as I posted it.
However, if you have something that you are happy with, that is fine.

 

[purinqui]

It shouldn't need that change as MINIFS is a valid function in Excel 2019 according to Microsoft's help on this function.
The code certainly worked for me as I posted it.
However, if you have something that you are happy with, that is fine.

View attachment 12765

It doesn`t work for me the "MINIFS" function.

But with the change that i did and i`ve posted in message #13; it works.

Have you run the code with function MINIFS? Or are just just being guided by the manual help of Microsoft office?.

 

[Peter_SSs]

Have you run the code with function MINIFS?

Yes but really No. I originally had a variable equal to the evaluate part of that code and viewed the result. However, to post the code I decided to go straight to the MsgBox and forgot to remove the "=" sign and must not have tested, sorry.
Just remove that "=" sign and try again. (I have tested this time. )

Sub Test5()
  MsgBox Evaluate("minifs(a1:a100,a1:a100,""<>0"")")
End Sub

 

[purinqui]

Yes but really No. I originally had a variable equal to the evaluate part of that code and viewed the result. However, to post the code I decided to go straight to the MsgBox and forgot to remove the "=" sign and must not have tested, sorry.
Just remove that "=" sign and try again. (I have tested this time. )

Sub Test5()
  MsgBox Evaluate("minifs(a1:a100,a1:a100,""<>0"")")
End Sub

When i try put this code for example in a variable X or with the funcion MsgBox, vba show me an error: "Run-Time error '13': Type Mismatch.

x=Evaluate("minifs(a1:a100,a1:a100,""<>0"")")

MsgBox Evaluate("minifs(a1:a100,a1:a100,""<>0"")")

But when i try put this another code in the same variable X or with function MsgBox, the code works fine.

x=Evaluate("min(if(a1:a100,a1:a100,""<>0""))")

MsgBox Evaluate("min(if(a1:a100,a1:a100,""<>0""))")

 

[purinqui]

Yes but really No. I originally had a variable equal to the evaluate part of that code and viewed the result. However, to post the code I decided to go straight to the MsgBox and forgot to remove the "=" sign and must not have tested, sorry.
Just remove that "=" sign and try again. (I have tested this time. )

Sub Test5()
  MsgBox Evaluate("minifs(a1:a100,a1:a100,""<>0"")")
End Sub

Sorry, I had tested it on another pc with XL2016 and that was the reason why it didn't work.

Now i ran the code in XL2019 and its works!. Thank you very much.

 

[Peter_SSs]

Sorry, I had tested it on another pc with XL2016 and that was the reason why it didn't work.

Pleased to see that it wasn't only me making errors in this thread.
Glad that you got it working in the end.