Copy Top 10 Values With Corresponding Names Into Another Sheet Excel VBA

[taccoo73]

Hi All;

First of all, thanks again for your time and help in advance. Here I am with another issue which I’m failing to see where the problem is.

Code below finds the highest 10 values in a range (column I) and copies them into another sheet. Part with the top 10 values and copying them is working nicely. But when I’m trying to bring the corresponding names for those values (column B) I’m hitting the wall. Code below says “Object Variable or With Block Variable not set”.

Sub top10()
 
Dim rngValues As Range
Dim rngNames As Range
Dim i As Integer
Dim r As Integer
Dim j As Long
Dim lrow As Long
Dim l As String
 
Dim rptdate As String
Dim CIOfile As String
Stop
rptdate = Range("B3")
CIOfile = "CIO Mng" & " " & rptdate & ".xlsm"
 
Set rngValues = Workbooks(CIOfile).Sheets("Rep").Range("I11:I310")
 
l = 0
j = 0
r = 19
 
For i = 1 To 10
 
    j = Application.WorksheetFunction.Large(rngValues, i)
    Set rngNames = rngValues.Find(j)
    lrow = rngNames.Row
    l = Cells(lrow, "B").Value
    Cells(r, "H") = j
    Cells(r, "G") = l
    r = r + 1
Next i
 
End Sub

Any help will be appreciated.

Kind Regards;

tac

 

[taccoo73]

Not sure if this will make any difference, but change this Dim as shown

Dim j As [COLOR=#0000ff]Double[/COLOR]

You're great! This change makes it work too without adding the xlFormulas part. Awesome!

 

[Fluff]

Glad to help & thanks for the feedback