Multiple if conditions statements

Question

I want to create a formula via excel vba for the below formula. However using RC formula, it takes longer time to refresh. What is the other method to speed up the formula. Create a column with header "Gap" and fill in formula below:- =IF(A2<>"2","NA",IF(AND(H2="F",U2<=0),"shortage",IF(AND(H2="E", U2<=0),C2,"OK")))

after that paste value of this column ?

Sub Level2Gap()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

Dim lastrow As Long
lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

Dim i As Long

Range("V2:V" & lastrow).Formula = "=IF(RC[-21]<>""2"",""NA"",IF(AND(RC[-14]=""F"",RC[-1]<=0),""shortage"",IF(AND(RC[-14]=""E"",RC[-1]<=0),RC[-19],""OK"")))"

End Sub

Answer 1

You should toggle Application.Calculation and Application.ScreenUpdating while you run your macro.

This article explains it better: Optimize VBA Code for performance improvement

Note: Range("V2:V" & lastrow) refers to the active worksheet and not necessarily Sheet1.

Example

Sub Level2Gap()
    Application.ScreenUpdating = False
    Dim savedCalcMode As XlCalculation
    savedCalcMode = Application.Calculation
    Application.Calculation = xlCalculationManual
    
    With ThisWorkbook.Worksheets("Sheet1")
        Dim lastrow As Long
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("V2:V" & lastrow).Formula = "=IF(RC[-21]<>""2"",""NA"",IF(AND(RC[-14]=""F"",RC[-1]<=0),""shortage"",IF(AND(RC[-14]=""E"",RC[-1]<=0),RC[-19],""OK"")))"
    End With
    
    Application.Calculation = savedCalcMode
    Application.ScreenUpdating = True  'It is no longer necessary to turn ScreenUpdating back on
End Sub