Solved: Finding the minimum in an excel array

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

JoeHart

Thread Starter
Joined
Aug 17, 2008
Messages
125
Hi,
I am stuck on something that I though was simple, I am trying to find the row of the lowest value in an excel array. In the excel sheet attached I would like to find the lowest number from B25:B70. I would then like to paste the value and associated L and C values into K1(value),L1(L-value),M1(C-value). In the attached example, my minimum value is 1500, my L-value = L: 5703 IND RIV 230 5705 STANTONE 230 1, and my C-value is C: 5703 IND RIV 230 5716 STANTONW 230 1. I found the code below on-line, but I don't know how to use a function in my subsystem or if it is close to what I want, but it's the closes I have found.

Thanks for your help,
Joe
.
Code:
   Function MinAddress(The_Range)

      ' Sets variable equal to minimum value in the input range.
      MinNum = Application.Min(The_Range)
      ' Loop to check each cell in the input range to see if equals the
      ' min variable.
      For Each cell In The_Range
         If cell = MinNum Then
            ' If the cell value equals the max variable it
            ' returns the address to the function and exits the loop
            MinAddress = cell.Address
            Exit For
         End If
      Next cell

   End Function
 

Attachments

OBP

Joined
Mar 8, 2005
Messages
19,895
Joe, why not just sort the data using column B ascending, which will always put the minimum value in B25?
 

JoeHart

Thread Starter
Joined
Aug 17, 2008
Messages
125
I am doing that in the macro I am using now. The problem is after I sort the L and C items are no longer in a place where I can distinguish them to be associated with the Items in the B column. By the way I am sorry if this does not come out right, for some reason my virus program on this computer blocks my ability to use tags. I think as long as I surround the items with quote and /quote in brackets it should still work. So this is a test. I found the code below, which I think will work. For some reason I am getting errors that I don't understand I got it off of http://excelvbamacro.com/finding-cell-with-minimummaximum-value-in-active-worksheet Sorry I don't know the tags for hyperlinks. Thanks for your help. I think you have helped me before, so thanks again.Sorry I tried to post the code but it did not work. I will have to do it from a different computer. For some reason when I wrap it with the code tags it sticks everything on one line.Joe[/quote
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Are you sorting all the data based on column B?
You can't just sort column b, it has to be all the data selected. You can have a hidden column with the original sort sequence if you want to put it back as it was after.
 

JoeHart

Thread Starter
Joined
Aug 17, 2008
Messages
125
Yes, if you look at the spreadsheet I attached their are blank in the B column with line up with the limiting element or L. These items don't sort correctly. This seems to be a lot harder than I originally thought. Sorting it does what I need but I have to do a little extra work after it is done. I guess I can write a formula in the next column so that the Limiting Element stays in line with the other two variables. So G28 would equal F27, I guess that would work.

Thanks again for your help,
Joe
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top