VBA Formula Takes too long

[Daniellel]

Hi,

I have written some VBA code to insert a formula into approx 450+ cells. The formula is...

Selection.FormulaArray = _
"=INDEX(TO3!$C$1:$I$9999,MATCH($A9&$B9,TO3!$A$1:$A$9999&TO3!$B$1:$B$9999,0),COLUMNS(TO3!$C2:C2))"

This has now added about 30 mins onto my VBA update and the list is only going to get bigger, is there anything i can do to speed this up???

 

[Trevor G]

Have you switched off auto update and also calculations to speed this up.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> autoUpdate()<br>Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>Application.Calculate = xlCalculationManual<br>Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br><br><SPAN style="color:#007F00">'Do your code then switch back on</SPAN><br><br>Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>Application.Calculate = xlCalculationAutomatic<br>Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Jon von der Heyden]

I'm assuming the area that this formula occupies spans multiple columns?

Why not split the MATCH into a helper column. That way you use a single helper column to yield the row index. Your INDEX formula can then refer to the helper column.

 

[Daniellel]

Have you switched off auto update and also calculations to speed this up.

Thank you for this but i am an absolute begginer on VBA and am trying to self teach, can you show exactly how this should look? I tried it and it didnt like it???

 

[Daniellel]

I'm assuming the area that this formula occupies spans multiple columns?

Why not split the MATCH into a helper column. That way you use a single helper column to yield the row index. Your INDEX formula can then refer to the helper column.

Again, Thank you for this but i am an absolute begginer on VBA and am trying to self teach, can you show exactly how this should look?

 

[Trevor G]

Thank you for this but i am an absolute begginer on VBA and am trying to self teach, can you show exactly how this should look? I tried it and it didnt like it???

You state you have a macro and I assume you have written that macro.

At the beginning of the Sub routine is where you place the application commands, exactly as I showed in my reply.

In the green text area is where you place in your code, as stated, then at the end of the code you add in the other part to place the commands back on.

 

[Jon von der Heyden]

What is the selection range that you are applying this to?

 

[Daniellel]

You state you have a macro and I assume you have written that macro.

At the beginning of the Sub routine is where you place the application commands, exactly as I showed in my reply.

In the green text area is where you place in your code, as stated, then at the end of the code you add in the other part to place the commands back on.

Thanks again,

This is showing an error message:

Compile Error : Expected Function or variable

 

[Daniellel]

Thanks again,

This is showing an error message:

Compile Error : Expected Function or variable

Sayin gthis whilst highlighting...

Calculate =

 

[Jon von der Heyden]

It should be calculation

Also I don't think you should include the DisplayAlerts bit because it may block potentially important alerts.