# Thread: Convert 2nd MinRange Formula to VBA Function

1. ## Convert 2nd MinRange Formula to VBA Function

Cells D411 has these Values, but on occasions it may have Values up to Cell 26.
4.90
26.10
11.60
10.10
9.80
5.50
6.90
3.90

Then current formula used to determine the 2nd lowest Value in that range is:
=MIN(IF(\$D\$4:\$D\$26>MIN(\$D\$4:\$D\$26),\$D\$4:\$D\$26))
This Formula is located in Cell D3

The VBA recorded Macro is, ( for Cell D3)
Selection.FormulaArray = "=MIN(IF(R4C4:R26C4>MIN(R4C4:R26C4),R4C4:R26C4 ))"

( learnt from this forum, thanks)
A Standard Function,syntax for Min only would be:

Sheets("Sheet1").Range("D3") = Application.Min(Range("Sheet1!D427"))

Question,
what is the syntax to have the Formula method of 2nd Min made to work like the Function

Note: fyi,
The formula,
=MIN(IF(\$D\$4:\$D\$26>MIN(\$D\$4:\$D\$26),\$D\$4:\$D\$26))
requires the keys Cntrl Shift Enter be pressed to make it work.

The VBA Record Macro method does'nt

Thanks

2. All I can solve it with so there is no formula on the sheet with minimal lines of code is :

Code:
```Sub 2ndMin ()
Selection.FormulaArray = "=MIN(IF(R4C4:R26C4>MIN(R4C4:R26C4),R4C4:R26C4))"

Sheets("Sheet1").Range("D3").Value = Sheets("Sheet1").Range("D3").Value

End sub```

3. Why not just the formula:
=SMALL(\$D\$4:\$D\$26,2)

Steve

4. ## The Following User Says Thank You to sdckapr For This Useful Post:

XPDiHard (2013-12-19)

5. sdsdckapr,
I tried your formula, but get
Run Time error 1004 - Method 'Range' of object'_Global' Failed
when I tried your formula this way;
Sheets("Sheet1").Range("D3") = Application.Small(Range("Sheet1!D426,2"))

On it's own as you suggested works fine, but leaves a resident formula in the sheet which is what I'm trying to avoid if possible

--------

If this syntax works using one line of code and no formula in sheet to obtain the lowest Value in that Range,
Sheets("Sheet1").Range("D2") = Application.Min(Range("Sheet1!D427"))

the next calculation is the 2nd lowest in Cell D3 of the same Range.

maybe it's not meant to work that way ?

Sheets("Sheet1").Range("D3") = Application.Small(Range("Sheet1!D426"),2)

Steve

7. ## Solved

makes sense now,

Thanks,

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•