VBA - Find 2nd minimum in list with duplicates

[JeanRene]

Hello,

I'd like to have the vba formula to find the row of the 2nd smallest number in a liste where number, e.g. if I have a list with 9;10;9;10;11; the formula would give me the row number of the first 10 it encounters.

I found an array formula using "large" and "if" to find the 2nd smallest if but it doesn't seem to work if there is only 2 values in the list (if I have 9;10;9;10 it shows 10 as result) and I don't know how to write it in vba...

Any help is welcome!

 

[JeanRene]

Hello Marcelo and Peter,

Thank you so much for your help, now it does work properly when I have 2 values, however I still have an issue if I have a decimal in the list: I have an error message "incompatibility type".
I don't really understand it, as if I put the formula directly in excel, it gives me the right answer, and also the dimension used in the vba for the small variant (Double) should be able to handle decimal, right?

Could it be something related to the version of Excel I use? (I have french version...)

 

[JeanRene]

After some tests, it seems it's the line

rw = Evaluate("match(" & Small2 & "," & Range(adr).EntireColumn.Address & ",0)")

which causes the issue...

 

[Peter_SSs]

After some tests, it seems it's the line

rw = Evaluate("match(" & Small2 & "," & Range(adr).EntireColumn.Address & ",0)")

which causes the issue...

What happens if you try

rw = Columns("A").Find(What:=Small2, LookIn:=xlValues, LookAt:=xlWhole).Row

 

[JeanRene]

It's working! Thank you very much!

Do you have an idea what caused the problem? Just for my knowledge

 

[Peter_SSs]

It's working! Thank you very much!

Do you have an idea what caused the problem? Just for my knowledge

You're welcome.

Probably related to a slight error in how Excel was holding the decimal numbers - along the lines of what is discussed here.

 

[JeanRene]

Ok

I have another question regarding this line: I would like to define a range in which to make the search instead of searching in a whole column. I defined a lmin and lmax variable which are the minimum row and maximum row of the range. I tried this syntax but it doesn't work:

rw = Range(Cells(lmin, "A"), Cells(lmax, "A")).Find(What:=min2, LookIn:=xlValues, LookAt:=xlWhole).Row

Could you provide some advice?

 

[Peter_SSs]

If the first part of the code is working to determine the correct value using the 'adr' string as the address of the range in question, then we can just use that to do the search. So try replacing that rw = ... line with these 3

With Range(adr)
  rw = .Find(What:=Small2, After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext).Row
End With

BTW, for the future, saying "it doesn't work" does not give helpers much to go on. Give additional information like
- Does it give an error message (what message?)
- Does it produce the wrong result? (what result and what result were you expecting?)
- Does it produce the right result but in the wrong place? (where was it and where should it have been?)
- Does it do nothing?
etc

 

[JeanRene]

Hello Peter,

You are right, a simple "it doesn't work" is not very helpful, my bad

I tried your new solution, I have an error message "Execution error '91' Variable object or variable block With not defined"

The previous try with

rw = Range("adr").Find(What:=min2, LookIn:=xlValues, LookAt:=xlWhole).Row

gave me an error message "Error execution '1004' Error defined by application or by object"

Here is the part of the code I use where the error '91' occurs (I changed the variable Small2 by min2):

          Do Until excess = 0                     
                    adr = "K" & lmin & ":K" & lmax & ""
                    min2 = Evaluate("aggregate(15,6," & adr & "/(" & adr & ">min(" & adr & ")),1)")
                    With Range(adr)
                    rw = .Find(What:=min2, After:=.Cells(.Cells.count), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext).Row
                    End With
                    Cells(lig + lmin - 1, 9) = Cells(lig + lmin - 1, 9) + 1
                    product = Cells(lig + lmin - 1, 2)
                    excess = excess - 1         
            Loop

 

[Peter_SSs]

Since that code has quite a bit more going on than the code I posted, so to have a chance of determining your problem, I would want to
- see the whole code
- know which line the error was occurring on
- see a small set of dummy data and its layout that the error is occurring with.

 

[JeanRene]

Hello Peter,

I finally manage to find a way to work:

Do Until surplus = 0                    
                    adr = "AF" & lmin & ":AF" & lmax & ""
                    If lmax - lmin = 0 Then
                    min = Application.WorksheetFunction.Subtotal(5, Range(Cells(lmin, "AF"), Cells(lmax, "AF")))
                    lig = Evaluate("=MATCH(SUBTOTAL(5,(AF" & lmin & ":AF" & lmax & ")),AF" & lmin & ":AF" & lmax & ",0)")
                    Cells(lig + lmin - 1, 30) = Cells(lig + lmin - 1, 30) + 1
                    surplus = surplus - 1
                    Else
                    min2 = Evaluate("aggregate(15,6," & adr & "/(" & adr & ">min(" & adr & ")),1)")
                    lig = Evaluate("match(aggregate(15,6," & adr & "/(" & adr & ">min(" & adr & ")),1)," & adr & ",0)")
                    Cells(lig + lmin - 1, 30) = Cells(lig + lmin - 1, 30) + 1
                    surplus = surplus - 1
                    End If
            Loop

I realised that the problem occured when the range I was analysing had only one row. I add an IF statement to go around this problem, and now it works fine.

Thank you again for your help, it has been very helpful