' Copy formula on the Sheet1 sheet down for all populated rows.
' Speeds this up using Arrays
Sub Copy_Formulae_Down_Array()
 
    Dim mycell As Variant
    Dim lastrow_Sheet2 As Long
    Dim lastrow_Sheet1 As Long
 
    Dim working As Variant
    Dim column_a_Sheet1 As Variant
    Dim column_d_Sheet1 As Variant
    Dim column_k_Sheet1 As Variant
    Dim column_l_Sheet1 As Variant
    Dim column_n_Sheet1 As Variant
    Dim column_o_Sheet1 As Variant
    Dim column_p_Sheet1 As Variant
    Dim column_r_Sheet1 As Variant
    Dim column_s_Sheet1 As Variant
    Dim column_ae_Sheet1 As Variant
    Dim column_ak_Sheet1 As Variant
    Dim column_al_Sheet1 As Variant
 
    Dim column_a_Sheet2 As Variant
    Dim column_f_Sheet2 As Variant
    Dim column_g_Sheet2 As Variant
    Dim column_i_Sheet2 As Variant
    Dim column_j_Sheet2 As Variant
    Dim column_ao_Sheet2 As Variant
 
    Dim n As Long
 
 
    ' Ask user.
    If ctrl_ask_before_running_subroutine = True Then
        If MsgBox("Copy post Sheet1 formulae down?", vbYesNo) = vbNo Then Exit Sub
    End If
 
 
    ' Update StatusBar.
    Application.StatusBar = "Copy post-Sheet1 Formulae down..."
 
 
    With Workbooks(wb_name)
 
        ' Get how many rows of data have been loaded into the sheet.
        lastrow_Sheet2 = Sheets("Sheet2").Cells(Rows.Count, 4).End(xlUp).Row
        ' Prevent line 2 being deleted - as this contains the formulae which need coping down later.
        If lastrow_Sheet2 < 3 Then
            lastrow_Sheet2 = 3
        End If
 
 
        With .Sheets("Sheet1")
 
            ' Activate the sheet.
            .Activate
 
            ' Get how many rows of data have been loaded into the sheet.
            lastrow_Sheet1 = .Cells(Rows.Count, 4).End(xlUp).Row
            ' Prevent line 2 being deleted - as this contains the formulae which need coping down later.
            If lastrow_Sheet1 < 3 Then
                lastrow_Sheet1 = 3
            End If
 
            ReDim working(1 To lastrow_Sheet1, 1) ' Working array
            working = Range("D3:D" & lastrow_Sheet1) ' load with dummy values.
 
            ReDim column_a_Sheet1(1 To lastrow_Sheet1, 1)
            ReDim column_k_Sheet1(1 To lastrow_Sheet1, 1)
            ReDim column_l_Sheet1(1 To lastrow_Sheet1, 1)
            ReDim column_n_Sheet1(1 To lastrow_Sheet1, 1)
            ReDim column_o_Sheet1(1 To lastrow_Sheet1, 1)
            ReDim column_p_Sheet1(1 To lastrow_Sheet1, 1)
            ReDim column_r_Sheet1(1 To lastrow_Sheet1, 1)
            ReDim column_s_Sheet1(1 To lastrow_Sheet1, 1)
            ReDim column_ae_Sheet1(1 To lastrow_Sheet1, 1)
            ReDim column_ak_Sheet1(1 To lastrow_Sheet1, 1)
            ReDim column_al_Sheet1(1 To lastrow_Sheet1, 1)
 
            ReDim column_a_Sheet2(1 To lastrow_Sheet2, 1)
            ReDim column_f_Sheet2(1 To lastrow_Sheet2, 1)
            ReDim column_g_Sheet2(1 To lastrow_Sheet2, 1)
            ReDim column_i_Sheet2(1 To lastrow_Sheet2, 1)
            ReDim column_j_Sheet2(1 To lastrow_Sheet2, 1)
            ReDim column_ao_Sheet2(1 To lastrow_Sheet2, 1)
 
 
            ' Column AE
            ' FX_Rate
            working = Range("AE3:AE" & lastrow_Sheet1)
            column_ae_Sheet1 = Range("AE3:AE" & lastrow_Sheet1)
            For n = LBound(working) To UBound(working)
              If Not column_ae_Sheet1(n, 1) Like "*[0-9]*" Then
                working(n, 1) = 1
              End If
            Next n
 
            .Range("AE3:AE" & lastrow_Sheet1).Value = working
 
 
 
 
            ' Column P - needed before column N
            ' =IF(R2="",K2*S2/100,K2*R2/100)
            column_r_Sheet1 = Range("R3:R" & lastrow_Sheet1)
            column_k_Sheet1 = Range("O3:O" & lastrow_Sheet1)
            column_s_Sheet1 = Range("S3:S" & lastrow_Sheet1)
            For n = LBound(working) To UBound(working)
                If column_r_Sheet1(n, 1) = "" Then
                    working(n, 1) = column_k_Sheet1(n, 1) * column_s_Sheet1(n, 1) / 100
                Else
                    working(n, 1) = column_k_Sheet1(n, 1) * column_r_Sheet1(n, 1) / 100
                End If
            Next n
 
            .Range("P3:P" & lastrow_Sheet1).Value = working
 
 
            ' Column N - needed before column M
            ' =P2*AE2
            column_p_Sheet1 = Range("P3:P" & lastrow_Sheet1)
            column_ae_Sheet1 = Range("AE3:AE" & lastrow_Sheet1)
            For n = LBound(working) To UBound(working)
                working(n, 1) = column_p_Sheet1(n, 1) * column_ae_Sheet1(n, 1)
            Next n
 
            .Range("N3:N" & lastrow_Sheet1).Value = working
 
 
            ' Column M
            '=L2-N2
            column_l_Sheet1 = Range("L3:L" & lastrow_Sheet1)
            column_n_Sheet1 = Range("N3:N" & lastrow_Sheet1)
            For n = LBound(working) To UBound(working)
                working(n, 1) = column_l_Sheet1(n, 1) - column_n_Sheet1(n, 1)
            Next n
 
            .Range("M3:M" & lastrow_Sheet1).Value = working
 
 
 
            ' Column Q
            ' =IF(R2="",S2*100,R2*100)
            column_r_Sheet1 = Range("R3:R" & lastrow_Sheet1)
            column_s_Sheet1 = Range("S3:S" & lastrow_Sheet1)
            For n = LBound(working) To UBound(working)
                If column_r_Sheet1(n, 1) = "" Then
                    working(n, 1) = column_s_Sheet1(n, 1) * 100
                Else
                    working(n, 1) = column_r_Sheet1(n, 1) * 100
                End If
            Next n
 
            .Range("Q3:Q" & lastrow_Sheet1).Value = working
 
 
 
            ' Column AI
            ' =IF(ISNA(VLOOKUP(D2,Sheet2!G$1:G$52435,1,FALSE)),"NOT FOUND","FOUND")
            column_d_Sheet1 = Range("D3:D" & lastrow_Sheet1)
            column_g_Sheet2 = Range("G3:G" & lastrow_Sheet2)
            For n = LBound(working) To UBound(working)
                working(n, 1) = ArrayFindEx(column_g_Sheet2, column_d_Sheet1(n, 1), "FOUND", "NOT FOUND")
            Next n
 
            .Range("AI3:AI" & lastrow_Sheet1).Value = working
 
 
 
            ' Column AJ
            'D2=D1
            .Range("AJ2:AJ2").AutoFill Destination:=.Range("AJ2:AJ" & lastrow_Sheet1)
 
 
            ' Column AK
            ' =IF(COUNTIF($A$1:A2,A2)>1,"AGGREGATE",SUMIF($A$1:$A$2227,A2,$L$1:$L$2227)-SUMIF(Sheet2!$A$1:$A$52435,A2,Sheet2!$F$1:$F$52435))
            column_a_Sheet1 = Range("A3:A" & lastrow_Sheet1)
            column_l_Sheet1 = Range("L3:L" & lastrow_Sheet1)
            column_a_Sheet2 = Sheets("Sheet2").Range("A3:A" & lastrow_Sheet2)
            column_f_Sheet2 = Sheets("Sheet2").Range("F3:F" & lastrow_Sheet2)
            For n = LBound(working) To UBound(working)
 
                If ArrayCountIf(column_a_Sheet1, column_a_Sheet1(n, 1)) > 1 Then
                    working(n, 1) = "AGGREGATE"
                Else
                    working(n, 1) = ArraySumIf(column_a_Sheet1, column_a_Sheet1(n, 1), column_l_Sheet1) - ArraySumIf(column_a_Sheet2, column_a_Sheet1(n, 1), column_f_Sheet2)
                End If
            Next n
 
            .Range("AK3:AK" & lastrow_Sheet1).Value = working
 
 
            ' Column AL
            ' =IF(A2=A1,AL1,IF(AND(AK2>-0.1,AK2<0.1),"MATCHED GROSS AMOUNT ISIN BY MONTH","GROSS AMOUNT NOT MATCHED ISIN BY MONTH"))
'            column_a_Sheet1 = Range("A3:A" & lastrow_Sheet1)
'            column_aj_Sheet1 = Range("AJ3:AJ" & lastrow_Sheet1)
'            column_ai_Sheet1 = Range("AI3:AJ" & lastrow_Sheet1)
'            For n = LBound(working) To UBound(working)
'                If column_a_Sheet1(n, 1) = column_a_Sheet1(n - 1, 1) Then
'                    working(n, 1) = column_aj_Sheet1(n - 1, 1)
'                Else
'                    If column_ai_Sheet1(n, 1) > -0.1 And column_ai_Sheet1(n, 1) < 0.1 Then
'                        working(n, 1) = "MATCHED GROSS AMOUNT ISIN BY MONTH"
'                    Else
'                        working(n, 1) = "GROSS AMOUNT NOT MATCHED ISIN BY MONTH"
'                    End If
'                End If
'            Next n
'
'            .Range("AM3:AM" & lastrow_Sheet1).Value = working
 
 
            .Range("AL2").AutoFill Destination:=.Range("AL2:AL" & lastrow_Sheet1)
            .Range("AL2:AL" & lastrow_Sheet1).Calculate
            .Range("AL3:AL" & lastrow_Sheet1) = .Range("AL3:AL" & lastrow_Sheet1).Value
 
 
 
            ' Column AM
            ' =IF(COUNTIF($A$1:A2,A2)>1,"AGGREGATE",SUMIF($A$1:$A$2227,A2,$K$1:$K$2227)-SUMIF(Sheet2!$A$1:A$52435,A2,Sheet2!$AO$1:$AO$52435))
            column_a_Sheet1 = Range("A3:A" & lastrow_Sheet1)
            column_k_Sheet1 = Range("K3:K" & lastrow_Sheet1)
            column_a_Sheet2 = Sheets("Sheet2").Range("A3:A" & lastrow_Sheet2)
            column_ao_Sheet2 = Sheets("Sheet2").Range("AO3:AO" & lastrow_Sheet2)
            For n = LBound(working) To UBound(working)
 
                If ArrayCountIf(column_a_Sheet1, column_a_Sheet1(n, 1)) > 1 Then
                    working(n, 1) = "AGGREGATE"
                Else
                    working(n, 1) = ArraySumIf(column_a_Sheet1, column_a_Sheet1(n, 1), column_k_Sheet1) - ArraySumIf(column_a_Sheet2, column_a_Sheet1(n, 1), column_ao_Sheet2)
                End If
            Next n
 
            .Range("AM3:AM" & lastrow_Sheet1).Value = working
 
 
 
            ' Column AR
            ' =IF(COUNTIF($A$1:A2,A2)>1,"AGGREGATE",SUMIF($A$1:$A$2227,A2,$P$1:$P$2227)-SUMIF(Sheet2!$A$1:$A$52435,A2,Sheet2!$J$1:$J$52435))
            column_a_Sheet1 = Range("A3:A" & lastrow_Sheet1)
            column_p_Sheet1 = Range("P3:P" & lastrow_Sheet1)
            column_a_Sheet2 = Sheets("Sheet2").Range("A3:A" & lastrow_Sheet2)
            column_j_Sheet2 = Sheets("Sheet2").Range("J3:J" & lastrow_Sheet2)
            For n = LBound(working) To UBound(working)
 
                If ArrayCountIf(column_a_Sheet1, column_a_Sheet1(n, 1)) > 1 Then
                    working(n, 1) = "AGGREGATE"
                Else
                    working(n, 1) = ArraySumIf(column_a_Sheet1, column_a_Sheet1(n, 1), column_p_Sheet1) - ArraySumIf(column_a_Sheet2, column_a_Sheet1(n, 1), column_j_Sheet2)
                End If
            Next n
 
            .Range("AR3:AR" & lastrow_Sheet1).Value = working
 
 
            ' Column AS
            ' =IF(A2=A1,AS1,IF(AND(AR2>-0.1,AR2<0.1),"MATCHED TAX AMOUNT ISIN BY MONTH","TAX AMOUNT NOT MATCHED ISIN BY MONTH"))
'            column_a_Sheet1 = Range("A3:A" & lastrow_Sheet1)
'            column_aq_Sheet1 = Range("AQ3:AQ" & lastrow_Sheet1)
'            column_ap_Sheet1 = Range("AP3:AP" & lastrow_Sheet1)
'            For n = LBound(working) To UBound(working)
'                If column_a_Sheet1(n, 1) = column_a_Sheet1(n - 1, 1) Then
'                    working(n, 1) = column_aq_Sheet1(n - 1, 1)
'                Else
'                    If column_ap_Sheet1(n, 1) > -0.1 And column_ap_Sheet1(n, 1) < 0.1 Then
'                        working(n, 1) = "MATCHED TAX AMOUNT ISIN BY MONTH"
'                    Else
'                        working(n, 1) = "TAX AMOUNT NOT MATCHED ISIN BY MONTH"
'                    End If
'                End If
'            Next n
'
'            .Range("AS3:AS" & lastrow_Sheet1).Value = working
 
 
            .Range("AS2").AutoFill Destination:=.Range("AS2:AS" & lastrow_Sheet1)
            .Range("AS2:AS" & lastrow_Sheet1).Calculate
            .Range("AS3:AS" & lastrow_Sheet1) = .Range("AS3:AS" & lastrow_Sheet1).Value
 
 
 
            ' Column AT
            ' =IF(COUNTIF($A$1:A2,A2)>1,"AGGREGATE",SUMIF($A$1:$A$2227,A2,$O$1:$O$2227)-SUMIF(Sheet2!$A$1:$A$52435,A2,Sheet2!$I$1:$I$52435))
            column_a_Sheet1 = Range("A3:A" & lastrow_Sheet1)
            column_o_Sheet1 = Range("O3:O" & lastrow_Sheet1)
            column_a_Sheet2 = Sheets("Sheet2").Range("A3:A" & lastrow_Sheet2)
            column_i_Sheet2 = Sheets("Sheet2").Range("I3:I" & lastrow_Sheet2)
            For n = LBound(working) To UBound(working)
 
                If ArrayCountIf(column_a_Sheet1, column_a_Sheet1(n, 1)) > 1 Then
                    working(n, 1) = "AGGREGATE"
                Else
                    working(n, 1) = ArraySumIf(column_a_Sheet1, column_a_Sheet1(n, 1), column_o_Sheet1) - ArraySumIf(column_a_Sheet2, column_a_Sheet1(n, 1), column_i_Sheet2)
                End If
            Next n
 
            .Range("AT3:AT" & lastrow_Sheet1).Value = working
 
        End With
    End With
 
 
    ' Clear all objects.
    Set mycell = Nothing
 
    Set working = Nothing
 
    Set column_a_Sheet1 = Nothing
    Set column_d_Sheet1 = Nothing
    Set column_k_Sheet1 = Nothing
    Set column_l_Sheet1 = Nothing
    Set column_n_Sheet1 = Nothing
    Set column_o_Sheet1 = Nothing
    Set column_p_Sheet1 = Nothing
    Set column_r_Sheet1 = Nothing
    Set column_s_Sheet1 = Nothing
    Set column_ae_Sheet1 = Nothing
    Set column_ak_Sheet1 = Nothing
    Set column_al_Sheet1 = Nothing
 
    Set column_a_Sheet2 = Nothing
    Set column_f_Sheet2 = Nothing
    Set column_i_Sheet2 = Nothing
    Set column_j_Sheet2 = Nothing
    Set column_ao_Sheet2 = Nothing
 
End Sub