Improve Efficiency of VBA Code

[Paul_Hossler]

I was saying you could do something like this

Option Explicit


Sub test()
    Dim ws As Worksheet
    Dim rData As Range
    Dim aryData As Variant
    Dim r As Long
    
    Application.ScreenUpdating = False
    
    Set ws = Worksheets("main workbook")


    'row 6 formulas cleared
    Set rData = ws.Cells(7, 1).CurrentRegion
    
    aryData = rData.Value

    For r = LBound(aryData, 1) + 1 To UBound(aryData, 1)
        aryData(r, 11) = Empty
        aryData(r, 15) = Empty
        aryData(r, 16) = Empty
        aryData(r, 29) = Empty
        aryData(r, 30) = Empty
        aryData(r, 31) = Empty
        aryData(r, 32) = Empty
        aryData(r, 33) = Empty
        aryData(r, 34) = Empty
        
        If aryData(r, 1) = 0 Then GoTo NextRow
            
        '11 - =IF(A6="","",IF(G6="","Excellent",CONCATENATE(H6," ",J6)))
        aryData(r, 11) = IIf(Len(aryData(r, 7)) = 0, "Excellent", aryData(r, 8) & " " & aryData(r, 10))
        
        '15 - =IF(A6="","",DATE(YEAR(TODAY()),MONTH(TODAY()),1))
        aryData(r, 15) = DateSerial(Year(Now), Month(Now), 1)
        
        '16 - =IF(A6="","",DATE(YEAR(O6),MONTH(O6)+1,0))
        aryData(r, 16) = DateSerial(Year(Now), Month(Now) + 1, 0)
        
        
        '29 - =IF(A6="","",IF(OR(R6="first",R6="second",R6="third"),ROUND(Y6*1250%,2),ROUND(Y6*950%,2)))
        Select Case LCase(aryData(r, 18))
            Case "first", "second", "third"
                aryData(r, 29) = Round(aryData(r, 25) * 12.5, 2)    '   not sure about your %
            Case Else
                aryData(r, 29) = Round(aryData(r, 25) * 9.5, 2)
        End Select
        
        '30 - =IF(A6="","",ROUND(Y6*10/12,2))
        aryData(r, 30) = Round(aryData(r, 25) * 10 / 12, 2)
        
        
        '31 - =IF(A6="","",IF(OR(R6="first",R6="second"),CEILING(ROUNDDOWN(Q6*13%,2),0.5),CEILING(ROUNDDOWN(Q6*2.5%,2),0.5)))
        With Application.WorksheetFunction
            Select Case LCase(aryData(r, 18))
                Case "first", "second"
                    aryData(r, 31) = .Ceiling(.RoundDown(aryData(r, 17) * 0.13, 2), 0.5)
                Case Else
                    aryData(r, 31) = .Ceiling(.RoundDown(aryData(r, 17) * 0.025, 2), 0.5)
            End Select
        End With
        
        '32 - =IF(A6="","",IF(OR(X6="Excellent",X6="very good",X6="good"),Z6,AA6))
        Select Case LCase(aryData(r, 24))
            Case "excellent", "very good", "good"
                aryData(r, 32) = aryData(r, 26)
            Case Else
                aryData(r, 32) = aryData(r, 27)
        End Select
        
        
        '33 - =IF(A6="","",ROUND(Y6*10/12,2))
        aryData(r, 33) = Round(aryData(r, 25) * 10 / 12, 2)
        
        '34 - =IF(A6="","",IF(OR(X6="Excellent",X6="very good",X6="good"),ROUND(AB6*375%,2),""))
        Select Case LCase(aryData(r, 24))
            Case "excellent", "very good", "good"
                aryData(r, 34) = Round(aryData(r, 28) * 3.75, 2)
        End Select
NextRow:
    Next r
    
    rData.Value = aryData
    
    Application.ScreenUpdating = True
End Sub