Second Smallest Number in a Set

  • Thread starter Thread starter J_Squared
  • Start date Start date
J

J_Squared

I need to find the second smallest number in a set of non-consecutive cells,
where all the cells are in the same row. I tried the SMALL function, but it
only allows arrays as inputs. I know I can use MIN to find the smallest, but
I am stumped on how to ge the second smallest. Any suggestions would be
greatly appreciated.
 
I used row 2 in the macro, but you can change it to any row. This puts the
values in a row into an array, then looks for the second smallest number in
that array.

Sub getSmall()
Dim myAry() As Variant, lc As Long, i As Long
lc = Cells(2, Columns.Count).End(xlToLeft).Column
ReDim myAry(lc)
For i = 1 To lc
If Cells(2, i) > "" Then
myAry(i - 1) = Cells(2, i).Value
End If
Next
x = WorksheetFunction.Small(myAry(), 2)
MsgBox x
End Sub

Put this in your standard code module1.
 
Hi,

My testing indicates that Small does work.

On the worksheet: =SMALL(A1:O1,2)

In VBA: MsgBox WorksheetFunction.Small(Range("A1:O1"), 2)

Your quote: "I tried the SMALL function, but it only allows arrays". A range
is actually an array.
 
Hi again,

I missed the part ablut the non consecutive cells until I saw the post by
JLGWhiz. However, the following also works:

In the worksheet:
=SMALL((A1:D1,F1:G1,I1:J1,L1,N1:O1),2)

In VBA:
MsgBox WorksheetFunction.Small(Range("A1:D1,F1:G1,I1:J1,L1,N1:O1"), 2)
 
Ossie, I believe that will only work if there are no empty cells in the
range. At least it was that way on my system.
 
My apologies, Ossie, apparently my original test had errors. It does work
with empty cells in the range. It ignores them the same as in the Min
function.
 
Hi JLGWhiz,

Thanks but I certainly don't expect apologies. Most of us make errors and I
believe that those that don't have never done anything. I have a little
saying "I would be rich if I received a dollar for each time I was right but
I would be broke if I had to return half a dollar for each time I was wrong.
However, how richer in knowledge I become if I learn from my mistakes."

On the serious side, initially I didn't test for the blank cells and it was
not until I saw your first post that I realised that the cells were
non-consecutive. I then tested using non-consecutive cells for the range and
hense my second post. Note in the worksheet formula that brackets are
required around the non-consecutive cells.
 
To expand on OssieMac's response... you can use single cell references as well)

=SMALL((A1,A4,A7:A11),2)

The key is to place the list of cell references in parentheses.
 
It did not even occur to me to put the reference cells in parentheses, which
absolutely makes sense. Thanks to everyone for all of your help.
 
Back
Top