Large function - when there is no nth value

Sneaky Pete

New Member
Joined
Jun 3, 2016
Messages
23
Hello everyone,

I've got a question about the large function in vba.
I've searched the internet but couldn't find an answer so I hope you can help.

In a sheet there's a range (column c) that contains numbers. The search range is fixed, the Numbers not.
I want to search for the highest and second highest value. No problem with the large function but when there is only 1 value in the range, the second highest gives an error 1004 because there is no second value.

What I want is an if then statement, something like:
If large("c10:c20",2) is nothing (no value/doesn't exists) the secondvalue is 0 esle secondvalue=large("c10:c20,2).

Hope anyone can help me with the code.

thanx
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,736
Office Version
2010
Platform
Windows
Code:
  Dim n             As Long
  Dim num1          As Double
  Dim num2          As Double

  With Range("C10:C20")
    n = WorksheetFunction.Count(.Cells)
    If n > 0 Then num1 = WorksheetFunction.Large(.Cells, 1)
    If n > 1 Then num2 = WorksheetFunction.Large(.Cells, 2)
  End With
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,658
Office Version
2010
Platform
Windows
Here is another way you could do it...
Code:
Dim num1          As Double
Dim num2          As Double

With Range("C10:C20")
  num1 = Evaluate("IFERROR(LARGE(" & .Address & ",1),0)")
  num2 = Evaluate("IFERROR(LARGE(" & .Address & ",2),0)")
End With
 

Sneaky Pete

New Member
Joined
Jun 3, 2016
Messages
23
Thank you both!
I've used the code from Rick and it works fine!
What I was wondering, how can I get the rownumber and/or address of the variable?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,658
Office Version
2010
Platform
Windows
I've used the code from Rick and it works fine!
What I was wondering, how can I get the rownumber and/or address of the variable?
Here is my code modified to return the Row numbers for the first and second largest value in the range (returns 0 if there is not second, or first, largest value)...
Code:
[table="width: 500"]
[tr]
	[td]Dim num1          As Double
Dim num2          As Double
Dim row1         As Long
Dim row2         As Long

With Range("C10:C20")
  On Error Resume Next
  num1 = Evaluate("IFERROR(LARGE(" & .Address & ",1),0)")
  row1 = .Row + Evaluate("MATCH(" & num1 & "," & .Address & ",0)") - 1
  num2 = Evaluate("IFERROR(LARGE(" & .Address & ",2),0)")
  row2 = .Row + Evaluate("MATCH(" & num2 & "," & .Address & ",0)") - 1
  On Error GoTo 0
End With
[/td]
[/tr]
[/table]
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,658
Office Version
2010
Platform
Windows
Here is my code modified to return the Row numbers for the first and second largest value in the range (returns 0 if there is not second, or first, largest value)...
Code:
[table="width: 500"]
[tr]
	[td]Dim num1          As Double
Dim num2          As Double
Dim row1         As Long
Dim row2         As Long

With Range("C10:C20")
  On Error Resume Next
  num1 = Evaluate("IFERROR(LARGE(" & .Address & ",1),0)")
  row1 = .Row + Evaluate("MATCH(" & num1 & "," & .Address & ",0)") - 1
  num2 = Evaluate("IFERROR(LARGE(" & .Address & ",2),0)")
  row2 = .Row + Evaluate("MATCH(" & num2 & "," & .Address & ",0)") - 1
  On Error GoTo 0
End With
[/td]
[/tr]
[/table]
Actually, the more I think about it, the more convinced I am that a modification of shg's approach is the better way to go for both your original question and your new question. Here is how I would write it...
Code:
Dim num1          As Double
Dim num2          As Double
Dim row1         As Long
Dim row2         As Long

With Range("C10:C20")
  On Error Resume Next
  num1 = Application.Large(.Cells, 1)
  row1 = .Row + Application.Match(num1, .Cells, 0) - 1
  num2 = Application.Large(.Cells, 2)
  row2 = .Row + Application.Match(num2, .Cells, 0) - 1
  On Error GoTo 0
End With
 
Last edited:

Sneaky Pete

New Member
Joined
Jun 3, 2016
Messages
23
Many thanx Rick!
I'm gonna try the code and implement it.
 

Sneaky Pete

New Member
Joined
Jun 3, 2016
Messages
23
Many thanks Rick,

I've implement it in my code and it works fine.
Now I've got one issue left.

In my code I've grouped some rows.
The rows contains merged cells.
The code I use is:
Rng.rows.showdetail = false
but iT returns an error (1004).

The code works fine at a single row so I think it's got something to do with the merged cells.

Do you know a solution or workaround?

Thanx in advanced.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,658
Office Version
2010
Platform
Windows
Many thanks Rick,

I've implement it in my code and it works fine.
Now I've got one issue left.

In my code I've grouped some rows.
The rows contains merged cells.
The code I use is:
Rng.rows.showdetail = false
but iT returns an error (1004).

The code works fine at a single row so I think it's got something to do with the merged cells.

Do you know a solution or workaround?
I've never used the ShowDetail property before, but according to the documentation, you can only apply it to a single summary row or column... if your merged cell spans more than one row, I would think that means you do not have a single row referenced. I have no idea about the possibility of a workaround (mainly because I am unfamiliar with the property), but I can tell you that merged cells almost always screw things up coding-wise... if you can do without using them, you would be better off in the long run.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,930
Messages
5,411,323
Members
403,360
Latest member
petroes

This Week's Hot Topics

Top