PDA

View Full Version : First 6 minimum values of a row in an array



hhh79bigo
08-07-2014, 03:26 AM
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
08-07-2014, 03:39 AM
A workbook would help.

hhh79bigo
08-07-2014, 03:43 AM
Sorry, please see attached

Bob Phillips
08-07-2014, 04:53 AM
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
08-07-2014, 05:21 AM
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
08-08-2014, 03:07 AM
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