' 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