Trying to reduce execution time

[msfarrar]

I am trying to reduce execution time for a for loop I have created. The loop corrects formulas in cells after data updates. The whole workbook takes about 13 minutes to run, including 10 of these FOR loops.

Any thoughts on style or procedure are greatly appreciated.

This will be used by other people, and I would like to make it as short of a run time as possible for the other users.

[vba]
'I am usinga with statement throughout the code to call the other worksheet.
'The array that I am using, it is initialized earlier in the code, this is just a list of the columns that I am concerned about correcting the code
effectivityArray2 = (4 5 10 11 16 17 18 19 20 21 22 23 26 27 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 4748 49)
'Only run the loop if these two values do not match
If wks.Cells(5, 4).Value <> .Cells(matchAOS(wks.Range("B5")), matchEff2(wks.Range("D1"))).Value Then
For j = LBound(effectivityArray2()) To UBound(effectivityArray2())
For i = 5 To UsedRange.Rows.Count - 1
'Determining the value of the column header, every other column has a number
If wks.Cells(1, effectivityArray2(j)).Value > 0 Then '
Returns the corresponding value from the other workbook
wks.Cells(i, effectivityArray2(j)).formula = "=" & .Cells(matchAOS(wks.Range("B" & i)), matchEff2(Cells(1, effectivityArray2(j)))).Address(External:=True)
Else
wks.Cells(i, effectivityArray2(j)).formula = "=" & .Cells(matchAOS(wks.Range("B" & i)), matchEff2(Cells(1, effectivityArray2(j) - 1)) + 1).Address(External:=True)
'Converting blank values to vbNullString after importing
If j < 7 Then
If wks.Cells(i, effectivityArray2(j)).Value = "12:00:00 AM" Then
wks.Cells(i, effectivityArray2(j)).formula = vbNullString
End If
End If
End If
Next i
Next j
End If [/vba]

[Kenneth Hobs]

See: http://vbaexpress.com/kb/getarticle.php?kb_id=1035

[msfarrar]

Wow that just reduced my run time down to 357 seconds!

Thank you!

[Teeroy]

You might get another speed increase by using a range object and offset rather than repeatedly referencing cells by position (although not as significant).
Then instead of For i = ... you can wrap the working in a Do loop
[VBA]
Do
'your code
set rng = rng.offset(1,0)
loop while rng <> ""[/VBA]

[Bob Phillips]

6 minutes is still a long time. After Kenneth's suggestion, your next savings would come from getting rid of the loops. You could try this approach in the code

- insert a helper columns
- in this column add a formula that checks the condition and inserts say 1 if true, blank if not
- set a range variable to the 1 values in the helper column - SpecialCells(xlCellTypeConstants)
- do a block update of the range value offset by the column (to get the real target column) with the formula - (take care to get the correct start row for your formula)
- delete the helper column