Solved

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

Posted on 2011-09-06
18
1,172 Views
Last Modified: 2012-05-12
The code I got is erroring

Can anyone advise.

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(" _
            & Rng1.Address & ">0," _
            & Rng1.Address & "))")
        End If
        Globals.Sheet1.Range("K2").Value = x
    End Sub

Open in new window

0
Comment
Question by:Jason
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 5
18 Comments
 
LVL 12

Expert Comment

by:snailcat
ID: 36493518
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

Open in new window

0
 
LVL 12

Expert Comment

by:snailcat
ID: 36493532
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

by:Jason
ID: 36493536
Yes will always be positive
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Expert Comment

by:snailcat
ID: 36493575
Then the code above should work. Please test with your data.
0
 

Author Comment

by:Jason
ID: 36493580
it returns a huge negative number

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


0
 
LVL 12

Expert Comment

by:snailcat
ID: 36493584
Can you post your spreadsheet?
0
 
LVL 12

Expert Comment

by:snailcat
ID: 36493595
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

Open in new window

0
 

Author Comment

by:Jason
ID: 36493989
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.

Function Code
sheet 1
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36494662
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

Open in new window

0
 

Author Comment

by:Jason
ID: 36494820
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

by:andrewssd3
ID: 36495887
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

by:Jason
ID: 36496389
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

by:snailcat
ID: 36496420
Renegade AD,

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

Accepted Solution

by:
andrewssd3 earned 500 total points
ID: 36496740
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(" & _
                      Rng1.Address & ">0," & Rng1.Address & "))"
       
        ' 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 & "," & _
            rngMatch.Address & ",1)"
        ' lookup the name from the ref range
        Range("J2").Formula = "=Index(" & rngRef.Address & "," & _
            rngMatch.Address & ",2)"
       
End Sub

Open in new window


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

by:andrewssd3
ID: 36496755
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

by:Jason
ID: 36499689
Andrew

There is a problem with xlR1C1

 error
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36500791
Forgot you were in VS - the enum for xlR1C1 is called xlReferenceStyle - so try Excel.xlReferenceStyle.xlR1C1
0
 

Author Closing Comment

by:Jason
ID: 36501088
Great Work Andrew

Thanks for your help works a dream
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question