# Convert INDEX, AGGREGATE to Macro

1. ## Convert INDEX, AGGREGATE to Macro

Not sure if this is possible, but I am looking to convert my function for 1 cell into a function for 365 cells. The current function references too many cells, which is slowing dozn my excel significantly.

Here is my current function:
=INDEX('Données forage wenco'!\$E:\$E,AGGREGATE(14,6,--('Données forage wenco'!\$C:\$C&'Données forage wenco'!\$D:\$D&'Données forage wenco'!\$G:\$G='Données forage'!\$D6&'Données forage'!Z\$4&'Données forage'!Z\$2)*ROW('Données forage wenco'!\$L:\$L),1))

It searches the 'Donneés forage wenco' data sheet being referenced, and 'Donneés forage' is the sheet where the output should be displayed. I am wondering if this can be made into a macro so that run time and lag can be cut down? The only cells that change are D6, as it will grow higher. If someone could convert this to a macro (if possible?) that would be highly appreciated.

2. ## Re: Convert INDEX, AGGREGATE to Macro

'Données forage wenco'!\$E:\$E
you refer to the whole column, which is >1 mln row in excel 2016.

Use a range that is great enough to match all data e.g.

'Données forage wenco'!\$E1:\$E5000

There are currently 1 users browsing this thread. (0 members and 1 guests)