How to increment the k in LARGE(array,k)
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
|