Solved: Speed up an Array Formula in VBA

[mangisqa]

Hello,

I have the following Code that works but takes a long time. Any suggestions on how to rewrite to speed it up?

[VBA]Range("A5").Activate
rangeToFill = "G5:G16000"
For Each cell In Range(rangeToFill)
cell.FormulaArray = "=INDEX('Work Orders'!$G$1:$G$6565, MATCH('Paste Full Report Here'!$A" & cell.Row & "&'Paste Full Report Here'!$D" & cell.Row & "&'Paste Full Report Here'!$E" & cell.Row & ",'Work Orders'!A:A&'Work Orders'!D&'Work Orders'!E:E,0))"
Next cel[/VBA]l


any suggestions would be greatly apprecitated!

Thanks!
Q

[CatDaddy]

wrap the entire code with

[VBA]
Application.ScreenUpdating = False
'All of your code
Application.ScreenUpdating = True
[/VBA]

[CatDaddy]

btw nice manipulation!

[p45cal]

I think this may speed things up a little (the formula's been changed):
[vba]Sub blah()
Range("G5:G16000").FormulaArray = "=INDEX('Work Orders'!R1C7:R6565C7, MATCH('Paste Full Report Here'!R5C1:R16000C1&'Paste Full Report Here'!R5C4:R16000C4&'Paste Full Report Here'!R5C5:R16000C5,'Work Orders'!R1C1:R6565C1&'Work Orders'!R1C4:R6565C4&'Work Orders'!R1C5:R6565C5,0))"
End Sub
[/vba]The ranges it uses may not be quite right, I've been trying to glean them from your original code anf formula.
btw, the help rules say the formulaArray string should be in R1C1 style, not A1 style.

[mangisqa]

Thank so much!

I do have the stop screen updating code in the entire thing already.

And the second manipulation absolutely helped make it go faster. I was not sure if my entire code then had to be in RC format if I changed it.


Thanks for helping a newbie out!

[p45cal]

I was not sure if my entire code then had to be in RC format if I changed it.

Nope.

[mangisqa]

another quick question, how can I manipulate R:C change with the row as the formula goes to each new cell?

I know I can use :
$A" & cell.Row & " in the other format. How does that translate?

[p45cal]

as the formula goes to each new cell?

You shouldn't need to, it's as if you're entering the the same formula into the whole of range G5:G16000 at once with Ctrl+Shift+Enter - so there should be no need to change it from cell to cell. I changed the formula to refer to all the cells it needs to, for the whole range. I had to guess a little as to what those ranges were so it may need some tweaking, but not of the cell-to-cell type, just the ranges it uses.
Record yourself adjusting the formula manually: Select that whole range, edit the formula (use the mouse as much as you can). The sheet doesn't have to be R1C1 style, nor does the formula you're editing, but the recorded macro will have R1C1 type formula for you to examine. Then tweak the code to get the same formula.

Or does it look like the formula is not going to give the right results?

[mangisqa]

Well the problem was it was returning all the same value- THEN I realized I had my calculation setting on manual so once I fixed that it worked well! I guess it has just been one of those weeks!

Thanks again!