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
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