# Large function - when there is no nth value

#### Sneaky Pete

##### New Member
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

### 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
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
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
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
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
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
Many thanx Rick!
I'm gonna try the code and implement it.

#### Sneaky Pete

##### New Member
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?

#### Rick Rothstein

##### MrExcel MVP
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.

Many Thanx!