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

 

[Fluff]

Try

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, , , xlWhole)
    Cells(r, "H") = j
    Cells(r, "G") = rngNames.Offset(, -7)
    r = r + 1
Next i
 
End Sub

 

[taccoo73]

Hi Fluff;

Thanks a lot for your time, appreciate it!

Still giving the same error “Object Variable or With Block Variable not set”. rngNames remains as "Nothing"

 

[Paul Ked]

If the corresponding values in Column B are on the same sheet as the top 10, then you need to qualify where:

    l = Workbooks(CIOfile).Sheets("Rep").Cells(lrow, "B").Value

 

[Fluff]

Hi Fluff;

Thanks a lot for your time, appreciate it!

Still giving the same error “Object Variable or With Block Variable not set”. rngNames remains as "Nothing"

Which line gives the error?
And what are the values in RngValues?

 

[taccoo73]

[COLOR=#333333]Cells(r, "G") = rngNames.Offset(, -7)[/COLOR]

this is the line with the error, when I check it with F8 in code/break, I can see that rngNames=Nothing.

Values in rngValues are numbers (column I), in current example from 36000 to 11000. Corresponding names are on column B, in the same sheet.

 

[Fluff]

Are the numbers the result of a formula?

 

[taccoo73]

Are the numbers the result of a formula?

Nope, they are in a Pivot Table.

 

[Fluff]

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

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

 

[taccoo73]

Found it!

Set rngNames = rngValues.Find(j, , xlFormulas, xlWhole)

makes it work. Tried to find the value 36000 by using Ctrl+F manually on the source sheet and I saw that excel cannot find it as Value, it was able to find when I change it to Formulas. Apparently values in pivot tables are not values after all.

Thank you very much for your time and help Fluff!