Code to Slooooooow

[Carlee]

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("D710").Value 'safety incidents
Range("AI14").Value = Worksheets("YCT").Range("D14").Value
'environmentl ops
Range("AI17:AI33").Value =
Worksheets("YCT").Range("D1733").Value 'daily operations
Range("AI36:AI46").Value =
Worksheets("YCT").Range("D3646").Value 'bioreactors
Range("AI49:AI51").Value =
Worksheets("YCT").Range("D4951").Value 'concentrate production
Range("AI54:AI60").Value =
Worksheets("YCT").Range("D5460").Value 'reagents used
Range("AI63:AI64").Value =
Worksheets("YCT").Range("D6364").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]

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]

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