Second Smallest Number in a Set

[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.

 

[JLGWhiz]

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.

 

[OssieMac]

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.

 

[OssieMac]

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((A11,F1:G1,I1:J1,L1,N1:O1),2)

In VBA:
MsgBox WorksheetFunction.Small(Range("A11,F1:G1,I1:J1,L1,N1:O1"), 2)

 

[JLGWhiz]

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.

 

[JLGWhiz]

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.

 

[OssieMac]

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.

 

[Rick Rothstein]

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.

 

[J_Squared]

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.