PDA

View Full Version : How can I find next small value(look up value) in a range?



juneve77
05-20-2018, 01:16 AM
I like to find a small value for look up value in a range, then use a next small value for next look up value and so on...
I don't know how to find..
please help me..
Thank you!!


Do untill (find a name)
small = Application.WorksheetFunction.Min(rng)
how can I find next small one?

ingrow = Application.WorksheetFunction.Match(small, rng, 0)

name = rng.Cells(ingrow, -13).Value


ss = Application.WorksheetFunction.VLookup(name, Range("sc"), i, 0)
sy = Application.WorksheetFunction.VLookup(name, Range("sc"), i + 1, 0)



If Worksheets("aa").Range("bb").Cells(y, x - 1) > 0 And _
ss = "S" And sy = "S" Then

Worksheets("aa").Range("bb").Cells(y, x) = name
Worksheets("cc").Cells(ingrow, 16) = Worksheets("cc").Cells(ingrow, 16) + 1


Else
find a next small value to find a name to use for lookup value..
loop

Thank you again..

mana
05-20-2018, 02:01 AM
Worksheetfunction.small(rng, 2)

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheetfunction-small-method-excel?f=255&MSPPError=-2147217396

juneve77
05-20-2018, 03:10 AM
Than you a lot.

But what if there are more than one same value in a range.

How can I use them first before the second smallest one?

Thank you~