Finding 10 largest/smallest values and populating another tab

[bigjdawg43]

Earlier today, someone helped me out with the below code, and i was VERY appreciative.

The idea is to find the rows of the 10 largest values in a column (x in the below code), and for each value/row, grab other information in the same row from other columns and populate another tab (Source1Y) with that info (that's the part with the use of Resize and Array). The problem is that I forgot to continue the conversation as to how to do the same exact thing, but also with the 10 smallest values in a column (my proposed y in the code below). I can attach a workbook if need be, but i have a feeling this is pretty easy to do, I just can't seem to figure it out.

So conceptually, i'd need

  .Range("C" & nr).Resize(, 6) = Array(ws.Range("A" & x), ws.Range("B" & x), ws.Range("C" & x), ws.Range("DT" & x), ws.Range("DU" & x), ws.Range("EB" & x))

for both x and y. What's the best way to do this? Appreciate any help. Thanks!

Dim ws As Worksheet
Dim AWF As WorksheetFunction
Dim i As Long, nr As Long, x As Long, y As Long

Application.ScreenUpdating = False
Set AWF = Application.WorksheetFunction

'Calculate 1Y contributors

For Each ws In ThisWorkbook.Sheets
    If ws.Name = "Port1" Or _
       ws.Name = "Port2" Then
        With ws
            For i = 1 To 10
                x = AWF.Match(AWF.Large(.Range("EB:EB"), i), .Range("EB:EB"), 0)
                y = AWF.Match(AWF.Small(.Range("EB:EB"), i), .Range("EB:EB"), 0)
                Debug.Print x
                Debug.Print y
                    With Sheets("Source1Y")
                        nr = .Cells(.Rows.Count, 3).End(xlUp).Row + 1
                        .Range("C" & nr).Resize(, 6) = Array(ws.Range("A" & x), ws.Range("B" & x), ws.Range("C" & x), ws.Range("DT" & x), ws.Range("DU" & x), ws.Range("EB" & x))
                    End With
            Next i
        End With
    End If
Next ws

End Sub

[FDibbins]

Not really sure why you need VBA for this, INDEX/MATCH/SMALL(large) will do the same thing for you

[bigjdawg43]

Right, it's part of a much larger workbook/process and was hoping to have it done programmatically. Think it's right there, just having issues with populating both large and small in the process.

[bigjdawg43]

To use INDEX/MATCH/SMALL(LARGE), i'd have to invoke INDIRECT to capture the correct tabs and that whole structure would be a mess for anybody using it other than me

[bigjdawg43]

Anybody have any ideas?

[bigjdawg43]

Figured this out. Kind of clunky, but separate loop for largest and smallest.