First 6 minimum values of a row in an array

[hhh79bigo]

I have an array 50x50 (currently)
In the first row I have a set of IDs in the first column I have another list of IDs, the array is populated with values that relate to the 2 IDs.
What I am trying to do is create a macro that for each row, it finds the first 6 smallest values and then finds the Column ID that is associated with that value and prints it to a table.

Below is the coding I have used. But it doesn't seem to work. and when it has finished the first loop of I it comes up with an error: "Run-time error '1004': Unable to get the match property of the WorksheetFunction class"

Where am I going wrong?

Thanks for your help in anticipation.

Owen

Sub Element()
   Dim ENDVAL As Integer, i As Integer, j As Integer
   Dim DOF(6) As Double, Master(6) As Double, slave(6) As Double
   ENDVAL = 50
   For i = 1 To ENDVAL
      For j = 1 To 6
         DOF(j) = WorksheetFunction.Small(Range("S19:BP19").Offset(i), j)
         Master(j) = i
         slave(j) = WorksheetFunction.Match(DOF(j), Range("S19:bP19").Offset(i))
         Range("Y" & Rows.Count).End(xlUp).Offset(1).Value = Master(j)
         Range("Z" & Rows.Count).End(xlUp).Offset(1).Value = slave(j)
      Next
   Next
End Sub

[Bob Phillips]

A workbook would help.

[hhh79bigo]

Sorry, please see attached

[Bob Phillips]

Forget VBA, use formulas.

In Y125, enter =ROW(A1)

Then select D125:AE125 and in the formula bar enter =SMALL(S20:BP20,{1,2,3,4,5,6}) and hit Ctrl-Shift-Enter (array-enter it).

Then copy Y125:AE125 down to Y174:AE174

[snb]

or:

Sub M_snb()
   sn = Sheet1.Cells(19, 16).CurrentRegion.Offset(, 2)
   ReDim sp(1 To UBound(sn) - 1, 1 To 7)
   
   For j = 2 To UBound(sn)
     sp(j - 1, 1) = j - 1
     For jj = 2 To 7
       sp(j - 1, jj) = Application.Match(Application.Small(Application.Index(sn, j, 0), jj), Application.Index(sn, j, 0), 0)
     Next
   Next
   
   Sheets.Add.Cells(1).Resize(UBound(sp), UBound(sp, 2)) = sp
End Sub

[snb]

Amended suggestioon:

Sub M_snb()
   sn = Sheet1.Cells(19, 16).CurrentRegion.Offset(, 3)
   ReDim sp(1 To UBound(sn) - 1, 1 To 7)
   
   For j = 2 To UBound(sn)
     For jj = 1 To 7
       sp(j - 1, jj) = Application.Match(Application.Small(Application.Index(sn, j, 0), jj), Application.Index(sn, j, 0), 0)
     Next
   Next
   
   Sheets.Add(, Sheets(Sheets.Count)).Cells(1).Resize(UBound(sp), UBound(sp, 2)) = sp
End Sub