Problem using the Large function in VB

**Alf** · 05-24-2012, 05:41 PM

I use this macro to rank the 15 highest numbers in a range of values.

Option Explicit

Sub find_max()
Dim MyRange As Range
Dim i As Integer
Dim j As Double

Set MyRange = Range("D4:D" & Range("D" & Rows.Count).End(xlUp).Row)

For i = 1 To 15
    j = Application.WorksheetFunction.Large(MyRange, i)
    MyRange.Find(What:=j, SearchOrder:=xlByRows).Activate
    ActiveCell.Offset(0, 1).Value = i
Next i

End Sub

It works fine if the cell contain a value i.e. 18298.9 but if it’s a formula like "D4=C4*B4"
I get “Run-time error ‘91’: Object variable or With block variable not set”

I’ve tried to add “LookIn:=xlFormulas” and “LookAt:=xlPart” but I still get the same error message.

Of course I could add a helper column converting formulas to values but I was hoping to find a solution as the Large function works with formulas in a “normal” Excel environment.

Alf

**tigeravatar** · 05-24-2012, 05:54 PM

Alf,

I'm not sure why you wouldn't just use the formula, but here's a modified version of your macro. The .Find method needs to use xlValues to find values resulting from formulas. I also included a change to provide unique ranking just in case there are any duplicate numbers in the top 15:

Sub find_max()
    
    Dim MyRange As Range
    Dim rngFound As Range
    Dim LargeIndex As Long
    Dim LargeValue As Long
    Dim LastVal As Long
    Dim OccurrenceNum As Long
    Dim i As Long
    
    Set MyRange = Range("D4", Cells(Rows.Count, "D").End(xlUp))
    
    For LargeIndex = 1 To 15
        LargeValue = Application.Large(MyRange, LargeIndex)
        If LargeValue <> LastVal Then
            MyRange.Find(LargeValue, , xlValues).Offset(, 1).Value = LargeIndex
            LastVal = LargeValue
            OccurrenceNum = 1
        Else
            OccurrenceNum = OccurrenceNum + 1
            Set rngFound = MyRange.Find(LargeValue, , xlValues)
            For i = 2 To OccurrenceNum
                Set rngFound = MyRange.Find(LargeValue, rngFound, xlValues)
            Next i
            rngFound.Offset(, 1).Value = LargeIndex
        End If
    Next LargeIndex
    
End Sub

**tigeravatar** · 05-24-2012, 06:03 PM

Alf,

Alternate, condensed version using the macro to input the formula, and then replacing the formula with its resulting values:

Sub tgr()
    
    With Range("D4", Cells(Rows.Count, "D").End(xlUp))
        .Offset(, 1).Formula = "=IF(RANK(D4," & .Address & ")+COUNTIF($D$4:D4,D4)-1>15,"""",RANK(D4," & .Address & ")+COUNTIF($D$4:D4,D4)-1)"
        .Offset(, 1).Value = .Offset(, 1).Value
    End With
    
End Sub