Large function - when there is no nth value

[Sneaky Pete]

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

 

[shg]

  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]

Here is another way you could do it...

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]

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]

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

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

 

[Rick Rothstein]

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

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

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

 

[Sneaky Pete]

Many thanx Rick!
I'm gonna try the code and implement it.

 

[Sneaky Pete]

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]

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.

 

[Sneaky Pete]

Many Thanx!