| |
| microsoft_excel:macros:set_formulae [2021/08/04 14:25] – created peter | microsoft_excel:macros:set_formulae [2021/08/04 15:20] (current) – removed peter |
|---|
| ====== Microsoft Excel - Macros - 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> | |
| |
