Excel VBA code to find the lowest number in range >0

Try this:

Public Sub lowerstNSW()

        Dim x As Integer
        Dim Rng1 As Range
       
        
        Set Rng1 = Range("E16:E30")

          x = excel.Evaluate("=MIN(IF(" _
           & Rng1.Address & ">0," _
            & Rng1.Address & "))")
       
     
       
       Sheet1.Range("K2").Value = x
       
       
              
    End Sub

Select allOpen in new window

I am assuming with the prior code that x is an integer.
Will you always have a positive number greater than 0 in the range?

If not what would you want the value of x to be?  (if your values were -5, -4, 0 then what should happen?)

Yes will always be positive

Then the code above should work. Please test with your data.

it returns a huge negative number

the lowest number in the range is 4.90 other than 2 zeros

Can you post your spreadsheet?

Here is the code that will return non-integer numbers :

Public Sub lowerstNSW()

        Dim x As Double
        
        Dim Rng1 As Range
       
        
        Set Rng1 = Range("E16:E30")

          x = Excel.Evaluate("=MIN(IF(" _
           & Rng1.Address & ">0," _
            & Rng1.Address & "))")
       
     
       
       Sheet1.Range("K2").Value = x
       
       
              
    End Sub

Select allOpen in new window

Cant post the spreadsheet you would not be able to use it.  It requires xml auth login to tab website

Did the change still comes up with huge negative number.

I may be missing something here, but why not just put the formula into cell K2 in one step like this:

Public Sub lowerstNSW()

        Dim x As Double
        
        Dim Rng1 As Range
       
        
        Set Rng1 = Range("E16:E30")

       Range("K2").FormulaArray = "=MIN(IF(" & _
                      Rng1.Address & ">0," & Rng1.Address & "))"
       
       
End Sub

Select allOpen in new window

andrew thanks heaps Great Job

now can you help with getting the name in I2 and the Box in H2

Based in the K2 results

The Range to search is C16: D30

I'm not quite clear exactly what you want here - I am assuming it is some sort of lookup from the row where the min value occurs.

I suggest you raise this as a new question with a proper explanation and close this one (awarding points as necessary!), as there should really only be one question per thread.  

Hi andrew

I am happy to do that will you be available to assist after all it was all stated in the initial posting

If you look at the spreadsheet img you can clearly see what I am asking

Renegade AD,

Do you want appropriate value in Column D to go to I2 and Column C to H2?

Sorry - meant to say that this does not address what would happen if the minimum value occurred 2 or more times in the reference table - this would just find the first one.

Andrew

There is a problem with xlR1C1

 

Forgot you were in VS - the enum for xlR1C1 is called xlReferenceStyle - so try Excel.xlReferenceStyle.xlR1C1

Great Work Andrew

Thanks for your help works a dream