Differences

This shows you the differences between two versions of the page.

--- microsoft_excel:macro_set_formulae [2020/07/15 09:30] – external edit 127.0.0.1
+++ microsoft_excel:macro_set_formulae [2021/08/04 14:25] (current) – removed peter
@@ Line 1: / Line 1: @@
-====== Microsoft Excel - Macro Set Formulae ======
-<code excel>
-' Sets formulae on Sheet1.
-.Range("A2").Formula = "=D2&"" - ""&TEXT(E2,""MMM"")&"" ""&YEAR(E2)"
-.Range("B2").Formula = "=A2&"" - ""&ROUND(L2,0)"
-.Range("C2").Formula = "=A2&"" - ""&G2"
-.Range("E2").Formula = "=TEXT(MID(F2,FIND("" "",F2)+1,FIND("","",F2)-FIND("" "",F2)-1)&"" ""&LEFT(F2,FIND("" "",F2)-1)&"" ""&RIGHT(F2,4),""dd/mm/yyyy"")"
-.Range("K2").Formula = "=I2+J2"
-.Range("L2").Formula = "=K2*AE2"
-.Range("M2").Formula = "=L2-N2"
-.Range("N2").Formula = "=P2*AE2"
-.Range("P2").Formula = "=IF(R2="""",K2*S2/100,K2*R2/100)"
-.Range("Q2").Formula = "=IF(R2="""",S2*100,R2*100)"
-.Range("AI2").Formula = "=IF(ISNA(VLOOKUP(D2,'Sheet2'!G$1:G$" & lastrow_Sheet2 & ",1,FALSE)),""NOT FOUND"",""FOUND"")"
-.Range("AJ2").Formula = "=D2=D1"
-.Range("AK2").Formula = "=IF(COUNTIF($A$1:A2,A2)>1,""AGGREGATE"",SUMIF($A$1:$A$" & lastrow_Sheet1 & ",A2,$L$1:$L$" & lastrow_Sheet1 & ")-SUMIF('Sheet2'!$A$1:$A$" & lastrow_Sheet2 & ",A2,'Sheet2'!$F$1:$F$" & lastrow_Sheet2 & "))"
-.Range("AL2").Formula = "=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""))"
-.Range("AM2").Formula = "=IF(COUNTIF($A$1:A2,A2)>1,""AGGREGATE"",SUMIF($A$1:$A$" & lastrow_Sheet1 & ",A2,$K$1:$K$" & lastrow_Sheet1 & ")-SUMIF('Sheet2'!$A$1:$A$" & lastrow_Sheet2 & ",A2,'Sheet2'!$AO$1:$AO$" & lastrow_Sheet2 & "))"
-.Range("AR2").Formula = "=IF(COUNTIF($A$1:A2,A2)>1,""AGGREGATE"",SUMIF($A$1:$A$" & lastrow_Sheet1 & ",A2,$P$1:$P$" & lastrow_Sheet1 & ")-SUMIF('Sheet2'!$A$1:$A$" & lastrow_Sheet2 & ",A2,'Sheet2'!$J$1:$J$" & lastrow_Sheet2 & "))"
-.Range("AS2").Formula = "=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""))"
-.Range("AT2").Formula = "=IF(COUNTIF($A$1:A2,A2)>1,""AGGREGATE"",SUMIF($A$1:$A$" & lastrow_Sheet1 & ",A2,$O$1:$O$" & lastrow_Sheet1 & ")-SUMIF('Sheet2'!$A$1:$A$" & lastrow_Sheet2 & ",A2,'Sheet2'!$I$1:$I$" & lastrow_Sheet2 & "))"
-</code>