msfarrar
08-28-2012, 12:15 PM
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.
'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
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.
'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