VBA code instead of formula to do calculations

[asad]

Hello All,

I have following code that works alright.

Sub Ladder()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Dim dt As Date


    Worksheets("Ticket Sales Ladder - Daily").Activate
        pw = InputBox("Please enter passowrd to run this code")
            If pw = "bus" Then
                dt = InputBox("Please enter date to run the ladder board for in dd/mm/yyyy format")
                    Range("C1").Value = dt
                    Range("C34").Value = dt
                    Range("A6").FormulaArray = "=IFERROR(SMALL(IF((StaffId > 0)*(date=$C$1)*((Location=$F$2)+(Location=$N$2)),StaffId),SUM(IF((StaffId > 0)*(date=$C$1)*((Location=$F$2)+(Location=$N$2))*(ISNUMBER(MATCH(StaffId,$A$4:A5,0))),1,0))+1),"""")"
                    Range("A6").Copy Range("A7:A28")
                    Range("C6").FormulaArray = "=IFERROR(IF(A6="""","""",INDEX(StaffNm,MATCH(1,(StaffId=A6)*(date=C$1),0))),"""")"
                    Range("C6").Copy Range("C7:C28")
                    Range("A6:E28").Calculate
                    Range("A5:A28").Value = Range("A5:A28").Value
                    Range("A38:E61").Calculate
                        
                        Range("F5").FormulaArray = "=SUM(IF((StaffId=$A5)*(date=$C$1)*(Location=$F$2)*(xTime > =$D5)*(xTime < $E5),1))"
                        Range("G5").FormulaArray = "=SUM(IF((StaffId=$A5)*(date=$C$1)*(Direction=""Round"")*(Location=$F$2)*(xTime > =$D5)*(xTime < $E5),1))"
                        Range("H5").Formula = "=IF(OR($G5=0,$F5=0),0,G5/F5)"
                        Range("I5").Formula = "=IF(G5=0,0,(F5*$H$1)-G5)"
                        Range("J5").Formula = "=If(I5 > 0,I5/7,"""")"
                        Range("K5").FormulaArray = "=SUM(IF((StaffId=$A5)*(date > =$B$1)*(date < =$C$1)*(Location=$K$2)*(xTime > =$D5)*(xTime < $E5),1))"
                        Range("L5").FormulaArray = "=SUM(IF((StaffId=$A5)*(date > =$B$1)*(date < =$C$1)*(Direction=""Round"")*(Location=$K$2)*(xTime > =$D5)*(xTime < $E5),1))"
                        Range("M5").Formula = "=IF(OR($L5=0,$K5=0),0,L5/K5)"
                        Range("N5").FormulaArray = "=SUM(IF((StaffId=$A5)*(date=$C$1)*(Location=$N$2)*(xTime > =$D5)*(xTime < $E5),1))"
                        Range("O5").FormulaArray = "=SUM(IF((StaffId=$A5)*(date=$C$1)*(Direction=""Round"")*(Location=$N$2)*(xTime > =$D5)*(xTime < $E5),1))"
                        Range("P5").Formula = "=IF(OR($O5=0,$N5=0),0,O5/N5)"
                        Range("Q5").Formula = "=IF(O5=0,0,(N5*$H$1)-O5)"
                        Range("R5").Formula = "=If(Q5 > 0,Q5/7,"""")"
                        Range("S5").FormulaArray = "=SUM(IF((StaffId=$A5)*(date > =$B$1)*(date < =$C$1)*(Location=$S$2)*(xTime > =$D5)*(xTime < $E5),1))"
                        Range("T5").FormulaArray = "=SUM(IF((StaffId=$A5)*(date > =$B$1)*(date < =$C$1)*(Direction=""Round"")*(Location=$S$2)*(xTime > =$D5)*(xTime < $E5),1))"
                        Range("U5").Formula = "=IF(OR($T5=0,$S5=0),0,T5/S5)"
           
                            Range("F5:U5").Copy Range("F6:U28")
                                Range("F5:U28").Calculate
                                Range("F5:U5").Copy Range("F38:U61")
                                Range("F38:U61").Calculate
                                    Range("F5:U28").Value = Range("F5:U28").Value
                                    Range("F38:U61").Value = Range("F38:U61").Value
Else: Exit Sub
End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic


End Sub

But it takes very long to do calculations. I am thinking of replacing formulas with VBA code to do the calculations. I tried replacing one formula at a time and the first one I tried, did not work. The formula I tried to replace was:

                    Range("A6").FormulaArray = "=IFERROR(SMALL(IF((StaffId > 0)*(date=$C$1)*((Location=$F$2)+(Location=$N$2)),StaffId),SUM(IF((StaffId > 0)*(date=$C$1)*((Location=$F$2)+(Location=$N$2))*(ISNUMBER(MATCH(StaffId,$A$4:A5,0))),1,0))+1),"""")"

And the code I tried was:

Sub Emp()

    For Each Cell In Range("A6:A28")
        If Range("StaffId") > Range("A5:A" & Cell.Row - 1).Value And Range("date") = Range("C1") And Range("Location") = Range("F2") Then
            Cell.Value = StaffId.xlMinimum
        End If
    Next


End Sub

And of course it did not work. Any suggestions to make it work will be much appreciated.

Thanks
Asad