Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts

    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
    method please ?

    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
    Last edited by XPDiHard; 2013-12-19 at 01:57.

  2. #2
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    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. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #4
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    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 ?

  6. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Your syntax is wrong:
    Sheets("Sheet1").Range("D3") = Application.Small(Range("Sheet1!D426"),2)

    Steve

  7. #6
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts

    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
  •