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