PDA

View Full Version : Solved: Speed up an Array Formula in VBA



mangisqa
07-19-2011, 12:50 PM
Hello,

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

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:D&'Work Orders'!E:E,0))"
Next cell


any suggestions would be greatly apprecitated!

Thanks!
Q

CatDaddy
07-19-2011, 01:23 PM
wrap the entire code with


Application.ScreenUpdating = False
'All of your code
Application.ScreenUpdating = True

CatDaddy
07-19-2011, 01:25 PM
btw nice manipulation!

p45cal
07-19-2011, 02:34 PM
I think this may speed things up a little (the formula's been changed):
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
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
07-20-2011, 05:26 AM
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
07-20-2011, 05:31 AM
I was not sure if my entire code then had to be in RC format if I changed it.Nope.

mangisqa
07-20-2011, 05:52 AM
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
07-20-2011, 07:25 AM
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
07-21-2011, 01:21 PM
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!