The Column_Large()code works, and is run on 300 rows.

Want to increment k value of the LARGE function in the formulas up to 20.

The Column_Large_20() code errors on the .Value in the first With statement.

Also, what can I expect the k value to do when it is run on 300 rows? Is that even feasible? Can it start over at MyArr(i) (i = 0)?

The general idea of the code is to find the LARGE value in the row range and return the Column Header to column A and the found LARGE value to column B.

The search data is on sheet1 the returns go to sheet2.

Thanks,
Howard


Sub Column_Large()

Dim lRowCount&
lRowCount = Sheets("Sheet1").Cells(Rows.Count, "K").End(xlUp).Row

With Sheets("Sheet2").Range("A2").Resize(lRowCount - 1)
.Formula = "=INDEX(Sheet1!$K$1:$KN$1,MATCH(LARGE(Sheet1!K2:KN 2,1),Sheet1!K2:KN2,0))": .Value = .Value
End With

With Sheets("Sheet2").Range("B2").Resize(lRowCount - 1)
.Formula = "=LARGE(Sheet1!K2:KN2,1)": .Value = .Value
End With

End Sub


Sub Column_Large_20()

Dim MyArr As Variant
Dim i As Long

MyArr = Array("1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,1 8,19,20")

Dim lRowCount&
lRowCount = Sheets("Sheet1").Cells(Rows.Count, "K").End(xlUp).Row


For i = LBound(MyArr) To UBound(MyArr)

Sheets("Sheet2").Range("A2").Resize (lRowCount - 1)
Sheets("Sheet2").Formula = "=INDEX(Sheet1!$K$1:$KN$1,MATCH(LARGE(Sheet1!K2:KN 2," & MyArr(i) & "),Sheet1!K2:KN2,0))": .Value = .Value

Sheets("Sheet2").Range("B2").Resize (lRowCount - 1)
Sheets("Sheet2").Formula = "=LARGE(Sheet1!K2:KN2" & MyArr(i) & ")": .Value = .Value

Next

End Sub