If Statement Optomization, Macro takes too long...

[cicconmr]

I'm assuming this is just a perpetual problem with all programs, but particularly worse with Visual Basic and the nature of excel.

I have the below code which tests a cell with a basic IF statement...if it is of a certain value, it sets values in other sheets. The problem is when iterating through 500+ Cells this code takes forever.

Any recomendations for speeding this up??

For Each Cell In ThisWorkbook.Worksheets("Portfolio Drop").Range("AB2:AB1000")
    
        If Cell.Value > mcLarge Then
        
            i = i + 1
            
            ThisWorkbook.Worksheets("Large Cap").Range("A" & i).Value = ThisWorkbook.Worksheets("Portfolio Drop").Range("A" & Cell.Row).Value
            ThisWorkbook.Worksheets("Large Cap").Range("C" & i).Value = ThisWorkbook.Worksheets("Portfolio Drop").Range("B" & Cell.Row).Value
        
        
        ElseIf Cell.Value > mcMidB And Cell.Value < mcLarge Then
        
            k = k + 1
            
            ThisWorkbook.Worksheets("Mid Cap").Range("A" & k).Value = ThisWorkbook.Worksheets("Portfolio Drop").Range("A" & Cell.Row).Value
            ThisWorkbook.Worksheets("Mid Cap").Range("C" & k).Value = ThisWorkbook.Worksheets("Portfolio Drop").Range("B" & Cell.Row).Value
        
        
        ElseIf Cell.Value < mcMidB And Cell.Value > 0 Then
        
            j = j + 1
            
            ThisWorkbook.Worksheets("Small & Micro Cap").Range("A" & j).Value = ThisWorkbook.Worksheets("Portfolio Drop").Range("A" & Cell.Row).Value
            ThisWorkbook.Worksheets("Small & Micro Cap").Range("C" & j).Value = ThisWorkbook.Worksheets("Portfolio Drop").Range("B" & Cell.Row).Value
        
        ElseIf Cell.Value = 0 Then
            
            tol = tol + 1
            
            If tol = 10 Then
                Exit Sub
            End If
        
        End If
    
    Next

 

[RoryA]

I suspect you probably need to turn off calculation at the beginning and turn it back on at the end.

 

[cicconmr]

Ingenious!

What is the way to do this via VBA? I'm assuming it's something like...application.calucation = manual or something?

Matt

 

[RoryA]

Application.calculation = xlcalculationmanual
' your code here
Application.calculation = xlcalculationautomatic

 

[Weaver]

Application.calculation = xlManual