Solved

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

Posted on 2011-09-06
1,016 Views
The code I got is erroring

First step
What I am trying to do is I have a range which I need to find the lowest value greater than 0

Second step
Then get the content of a range in that row and display in different cells
I haven't started step to still trying to resolve step 1

``````Public Sub lowerstNSW()

Dim x, Rng1
Dim excel As New Microsoft.Office.Interop.Excel.Application
Rng1 = Globals.Sheet1.Range("E16:E30")
x = excel.Min(Rng1)
If x = 0 Then
x = excel.Evaluate("=MIN(IF(" _
End If
Globals.Sheet1.Range("K2").Value = x
End Sub
``````
0
Question by:Jason
• 7
• 6
• 5

LVL 12

Expert Comment

Try this:
``````Public Sub lowerstNSW()

Dim x As Integer
Dim Rng1 As Range

Set Rng1 = Range("E16:E30")

x = excel.Evaluate("=MIN(IF(" _

Sheet1.Range("K2").Value = x

End Sub
``````
0

LVL 12

Expert Comment

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?)

0

Author Comment

Yes will always be positive
0

LVL 12

Expert Comment

0

Author Comment

it returns a huge negative number

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

0

LVL 12

Expert Comment

0

LVL 12

Expert Comment

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(" _

Sheet1.Range("K2").Value = x

End Sub
``````
0

Author Comment

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.

0

LVL 17

Expert Comment

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(" & _

End Sub
``````
0

Author Comment

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
0

LVL 17

Expert Comment

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.
0

Author Comment

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

0

LVL 12

Expert Comment

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

LVL 17

Accepted Solution

andrewssd3 earned 500 total points
OK - I've had a closer look at your screenshot and I think I can see what you want, so try this:

``````Public Sub lowerstNSW()

Dim x As Double

Dim rngRef As Range
Dim Rng1 As Range
Dim rngMatch As Range

Set rngRef = Range("C16:E30")

Set Rng1 = rngRef.Columns(3).Cells

Range("K2").FormulaArray = "=MIN(IF(" & _

' get the row in the reference range where this value was found
' you can hide the column later if you want
Set rngMatch = Range("L2")
rngMatch.FormulaR1C1 = "=Match(RC[-1]," & Rng1.Address(True, True, xlR1C1) & ",0)"

' lookup the number (box?) from the ref range
Range("I2").Formula = "=Index(" & rngRef.Address & "," & _
' lookup the name from the ref range
Range("J2").Formula = "=Index(" & rngRef.Address & "," & _

End Sub
``````

I I was writing this from scratch I would get rid of all the literal cell references and parameterise the routine a bit so that it was easier to change to locations in the future, but I think this illustrates the principle.
0

LVL 17

Expert Comment

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.
0

Author Comment

Andrew

There is a problem with xlR1C1

0

LVL 17

Expert Comment

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

Author Closing Comment

Great Work Andrew

Thanks for your help works a dream
0