Code to Slooooooow

Carlee · Dec 11, 2007

I use the following nested if statement to pull values from one sheet, based
on a criteria. The if statement is super slow (2 minutes to run). I have
tried a case statement and it is about as slow.

What can i do to speed this up?

If Range("B4") = "January" Then
Range("AI7:AI10").Value =
Worksheets("YCT").Range("C7:I10").Value 'safety incidents
Range("AI14").Value = Worksheets("YCT").Range("C14").Value
'environmentl ops
Range("AI17:AI33").Value =
Worksheets("YCT").Range("C17:C33").Value 'daily operations
Range("AI36:AI46").Value =
Worksheets("YCT").Range("C36:C46").Value 'bioreactors
Range("AI49:AI51").Value =
Worksheets("YCT").Range("C49:C51").Value 'concentrate production
Range("AI54:AI60").Value =
Worksheets("YCT").Range("C54:C60").Value 'reagents used
Range("AI63:AI64").Value =
Worksheets("YCT").Range("C63:C64").Value 'maintenance
ElseIf Range("B4") = "February" Then
Range("AI7:AI10").Value =
Worksheets("YCT").Range("D7

10").Value 'safety incidents
Range("AI14").Value = Worksheets("YCT").Range("D14").Value
'environmentl ops
Range("AI17:AI33").Value =
Worksheets("YCT").Range("D17

33").Value 'daily operations
Range("AI36:AI46").Value =
Worksheets("YCT").Range("D36

46").Value 'bioreactors
Range("AI49:AI51").Value =
Worksheets("YCT").Range("D49

51").Value 'concentrate production
Range("AI54:AI60").Value =
Worksheets("YCT").Range("D54

60").Value 'reagents used
Range("AI63:AI64").Value =
Worksheets("YCT").Range("D63

64").Value 'maintenance
ElseIf Range("B4") = "March" Then
Range("AI7:AI10").Value =
Worksheets("YCT").Range("E7:E10").Value 'safety incidents
Range("AI14").Value = Worksheets("YCT").Range("E14").Value
'environmentl ops
Range("AI17:AI33").Value =
Worksheets("YCT").Range("E17:E33").Value 'daily operations
Range("AI36:AI46").Value =
Worksheets("YCT").Range("E36:E46").Value 'bioreactors
Range("AI49:AI51").Value =
Worksheets("YCT").Range("E49:E51").Value 'concentrate production
Range("AI54:AI60").Value =
Worksheets("YCT").Range("E54:E60").Value 'reagents used
Range("AI63:AI64").Value =
Worksheets("YCT").Range("E63:E64").Value 'maintenance
ElseIf Range("B4") = "April" Then
Range("AI7:AI10").Value =
Worksheets("YCT").Range("F7:F10").Value 'safety incidents
Range("AI14").Value = Worksheets("YCT").Range("F14").Value
'environmentl ops
Range("AI17:AI33").Value =
Worksheets("YCT").Range("F17:F33").Value 'daily operations
Range("AI36:AI46").Value =
Worksheets("YCT").Range("F36:F46").Value 'bioreactors
Range("AI49:AI51").Value =
Worksheets("YCT").Range("F49:F51").Value 'concentrate production
Range("AI54:AI60").Value =
Worksheets("YCT").Range("F54:F60").Value 'reagents used
Range("AI63:AI64").Value =
Worksheets("YCT").Range("F63:F64").Value 'maintenance
ElseIf Range("B4") = "May" Then
Range("AI7:AI10").Value =
Worksheets("YCT").Range("G7:G10").Value 'safety incidents
Range("AI14").Value = Worksheets("YCT").Range("G14").Value
'environmentl ops
Range("AI17:AI33").Value =
Worksheets("YCT").Range("G17:G33").Value 'daily operations
Range("AI36:AI46").Value =
Worksheets("YCT").Range("G36:G46").Value 'bioreactors
Range("AI49:AI51").Value =
Worksheets("YCT").Range("G49:G51").Value 'concentrate production
Range("AI54:AI60").Value =
Worksheets("YCT").Range("G54:G60").Value 'reagents used
Range("AI63:AI64").Value =
Worksheets("YCT").Range("G63:G64").Value 'maintenance
ElseIf Range("B4") = "June" Then
Range("AI7:AI10").Value =
Worksheets("YCT").Range("H7:H10").Value 'safety incidents
Range("AI14").Value = Worksheets("YCT").Range("I14").Value
'environmentl ops
Range("AI17:AI33").Value =
Worksheets("YCT").Range("H17:H33").Value 'daily operations
Range("AI36:AI46").Value =
Worksheets("YCT").Range("H36:H46").Value 'bioreactors
Range("AI49:AI51").Value =
Worksheets("YCT").Range("H49:H51").Value 'concentrate production
Range("AI54:AI60").Value =
Worksheets("YCT").Range("H54:60").Value 'reagents used
Range("AI63:AI64").Value =
Worksheets("YCT").Range("H63:H64").Value 'maintenance
ElseIf Range("B4") = "July" Then
Range("AI7:AI10").Value =
Worksheets("YCT").Range("I7:I10").Value 'safety incidents
Range("AI14").Value = Worksheets("YCT").Range("I14").Value
'environmentl ops
Range("AI17:AI33").Value =
Worksheets("YCT").Range("I17:I33").Value 'daily operations
Range("AI36:AI46").Value =
Worksheets("YCT").Range("I36:I46").Value 'bioreactors
Range("AI49:AI51").Value =
Worksheets("YCT").Range("I49:I51").Value 'concentrate production
Range("AI54:AI60").Value =
Worksheets("YCT").Range("I54:I60").Value 'reagents used
Range("AI63:AI64").Value =
Worksheets("YCT").Range("I63:I64").Value 'maintenance
ElseIf Range("B4") = "August" Then
Range("AI7:AI10").Value =
Worksheets("YCT").Range("J7:J10").Value 'safety incidents
Range("AI14").Value = Worksheets("YCT").Range("J14").Value
'environmentl ops
Range("AI17:AI33").Value =
Worksheets("YCT").Range("J17:J33").Value 'daily operations
Range("AI36:AI46").Value =
Worksheets("YCT").Range("J36:J46").Value 'bioreactors
Range("AI49:AI51").Value =
Worksheets("YCT").Range("J49:J51").Value 'concentrate production
Range("AI54:AI60").Value =
Worksheets("YCT").Range("J54:J60").Value 'reagents used
Range("AI63:AI64").Value =
Worksheets("YCT").Range("J63:J64").Value 'maintenance
ElseIf Range("B4") = "September" Then
Range("AI7:AI10").Value =
Worksheets("YCT").Range("K7:K10").Value 'safety incidents
Range("AI14").Value = Worksheets("YCT").Range("K14").Value
'environmentl ops
Range("AI17:AI33").Value =
Worksheets("YCT").Range("K17:K33").Value 'daily operations
Range("AI36:AI46").Value =
Worksheets("YCT").Range("K36:K46").Value 'bioreactors
Range("AI49:AI51").Value =
Worksheets("YCT").Range("K49:K51").Value 'concentrate production
Range("AI54:AI60").Value =
Worksheets("YCT").Range("K54:K60").Value 'reagents used
Range("AI63:AI64").Value =
Worksheets("YCT").Range("K63:K64").Value 'maintenance
ElseIf Range("B4") = "October" Then
Range("AI7:AI10").Value =
Worksheets("YCT").Range("L7:L10").Value 'safety incidents
Range("AI14").Value = Worksheets("YCT").Range("L14").Value
'environmentl ops
Range("AI17:AI33").Value =
Worksheets("YCT").Range("L17:L33").Value 'daily operations
Range("AI36:AI46").Value =
Worksheets("YCT").Range("L36:L46").Value 'bioreactors
Range("AI49:AI51").Value =
Worksheets("YCT").Range("L49:L51").Value 'concentrate production
Range("AI54:AI60").Value =
Worksheets("YCT").Range("L54:L60").Value 'reagents used
Range("AI63:AI64").Value =
Worksheets("YCT").Range("L63:L64").Value 'maintenance
ElseIf Range("B4") = "November" Then
Range("AI7:AI10").Value =
Worksheets("YCT").Range("M7:M10").Value 'safety incidents
Range("AI14").Value = Worksheets("YCT").Range("M14").Value
'environmentl ops
Range("AI17:AI33").Value =
Worksheets("YCT").Range("M17:M33").Value 'daily operations
Range("AI36:AI46").Value =
Worksheets("YCT").Range("M36:M46").Value 'bioreactors
Range("AI49:AI51").Value =
Worksheets("YCT").Range("M49:M51").Value 'concentrate production
Range("AI54:AI60").Value =
Worksheets("YCT").Range("M54:M60").Value 'reagents used
Range("AI63:AI64").Value =
Worksheets("YCT").Range("M63:M64").Value 'maintenance
ElseIf Range("B4") = "December" Then
Range("AI7:AI10").Value =
Worksheets("YCT").Range("N7:N10").Value 'safety incidents
Range("AI14").Value = Worksheets("YCT").Range("N14").Value
'environmentl ops
Range("AI17:AI33").Value =
Worksheets("YCT").Range("N17:N33").Value 'daily operations
Range("AI36:AI46").Value =
Worksheets("YCT").Range("N36:N46").Value 'bioreactors
Range("AI49:AI51").Value =
Worksheets("YCT").Range("N49:N51").Value 'concentrate production
Range("AI54:AI60").Value =
Worksheets("YCT").Range("N54:N60").Value 'reagents used
Range("AI63:AI64").Value =
Worksheets("YCT").Range("N63:N64").Value 'maintenance
Else
Exit Function
End If

Tim Williams · Dec 11, 2007

Try something like this:

Dim x as integer
Dim ws as worksheet
set ws=Worksheets("YCT")

Select Case Range("B4").value
Case "January": x=1
Case "February": x=2
Case "March": x=3
...
Case "December": x=12
Case Else: x=-1
End select

'handle -1 case by msgbox and exit....

If x <> -1 Then

Range("AI7:AI10").Value = ws.Range("C7:I10").Offset(0, x-1).Value
...
Range("AI63:AI64").Value = ws.Range("C63:C64").Offset(0, x-1).Value

End If

Tim

Andrew Taylor · Dec 11, 2007

It could be that setting the values of the target cells causes
some lengthy recalculation, in which case it might help to turn
off automatic calculation. Turning off screen updating might
help a little too:

Application.ScreenUpdating = False
Dim saveCalcMode
' remember initial calc mode
saveCalcMode = Application.Calculation
Application.Calculation = xlCalculationManual
'
' your code here
'
' restore initial calc mode
Application.Calculation = saveCalcMode
Application.ScreenUpdating = True

Code to Slooooooow

Carlee

Tim Williams

Andrew Taylor