sharky12345
Well-known Member
- Joined
- Aug 5, 2010
- Messages
- 3,404
- Office Version
- 2016
- Platform
- Windows
I'm using this to populate a column but it takes ages to run as there are over 15000 rows - is it possible to turn this into a worksheetfunction and if yes I assume it will run a lot quicker?
VBA Code:
Range("S2").Formula = "=IFERROR(INDEX(DATA!F2:F" & LastDataRow & ",AGGREGATE(14,6,(ROW(DATA!D2:D" & LastDataRow & ")-ROW(DATA!D2)+1)/(DATA!C2:C" & LastDataRow & "=A2)/(DATA!D2:D" & LastDataRow & "<G2),1)),"""")"
Range("S2").AutoFill Destination:=Range("S2:S" & LastRow), Type:=xlFillDefault
Application.Calculation = xlCalculationAutomatic
Range("S2:S" & LastRow).Value = Range("S2:S" & LastRow).Value