Converting a Function to VBA Code

Sergiu G

New Member
Joined
Jun 13, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
Hello ExcelChamps,


I've managed to do a complex formula in which I'm calculating the Amount/month.

The formula: =IFERROR(IF(OR($D4="Both",$D4="P&L"),$L4/($I4-$H4)*($AD$1=MEDIAN(EOMONTH($H4,-1)+1,EOMONTH($I4,0),$AD$1))*(MIN($I4,EOMONTH($AD$1,0))-MAX($H4,$AD$1-1)),0),IF(AND(EOMONTH($H4,0)=EOMONTH($I4,0),EOMONTH($H4,0)=EOMONTH($AD$1,0)),$L4,0))



Using that, I'm calculating the Amount/Month:

1. The Reporting Type should be "Both" or "P&L"

2. I'm calculating the Amount/Day

3. SumProduct the amount/day with the number of days in the month based on the starting date and end date

4. SumIf - based on Outflow/Inflow and the Element Type will be included too

Can I convert that to VBA Macro?

That is the table:

Inflow/OutflowElement TypeForecast IDReporting TypeElement DescriptionTransaction DateRepresentative MonthP&L Start DateP&L End DateNET AmountVAT AmountGross AmountPaying EntityID CountDate ValidatorCost Type Transaction StatusPayment to NGI RO
OutflowTechnology ServicesBothAtlassian2020/05/07May2020/05/042020/06/031500.001500.00UKRecurring DDActualN/A
OutflowTechnology ServicesBothQA Software2020/05/12May2020/05/122020/06/11100.00100.00UKRecurring DDActualN/A
OutflowOffice CostsBothEquipment bought from Amazon2020/05/22May2020/05/222020/11/211200.001200.00UKOne Time PaymentActualN/A
OutflowOffice CostsBothATTIC SELF STORAGE Rent2020/05/24May2020/04/212020/05/21200.00200.00UKRecurring DDActualN/A
OutflowTechnology ServicesBothO3652020/05/24May2020/04/202020/05/20150.00150.00UKRecurring DDActualN/A
OutflowOffice CostsBothMarketing Strategic2020/05/28May2020/05/282020/05/2750.0050.00ROOne Time PaymentActualN/A
OutflowTechnology ServicesBothVodafone2020/06/05May2020/05/012020/05/3170.0070.00UKRecurring DDActualN/A
OutflowHR CostCashRetainer Payout Jan 2020-Jun 20202020/06/09N/AN/AN/A300.00300.00UKOne Time PaymentActualN/A
InflowNGI Uk RevenueBothGood Box Projects 2020/06/26May2020/05/012020/08/3125000.0025000.00UKOne Time PaymentActualN/A
InflowNGI Uk RevenueBothNando's NM0232020/07/22May2020/05/012020/05/312000.002000.00UKOne Time PaymentActualN/A
OutflowHR CostP&LTCO - DeltaN/AMay01/05/202031/05/2020100.00100.00N/AN/AActualN/A


Here are the formulas which calculate the Amount/Month for each item:

Cell Formulas
RangeFormula
U2:U11U2=IFERROR(IF(OR($D2="Both",$D2="P&L"),$L2/($I2-$H2)*($U$1=MEDIAN(EOMONTH($H2,-1)+1,EOMONTH($I2,0),$U$1))*(MIN($I2,EOMONTH($U$1,0))-MAX($H2,$U$1-1)),0),IF(AND(EOMONTH($H2,0)=EOMONTH($I2,0),EOMONTH($H2,0)=EOMONTH($U$1,0)),$L2,0))
V2:V11V2=IFERROR(IF(OR($D2="Both",$D2="P&L"),$L2/($I2-$H2)*($V$1=MEDIAN(EOMONTH($H2,-1)+1,EOMONTH($I2,0),$V$1))*(MIN($I2,EOMONTH($V$1,0))-MAX($H2,$V$1-1)),0),IF(AND(EOMONTH($H2,0)=EOMONTH($I2,0),EOMONTH($H2,0)=EOMONTH($V$1,0)),$L2,0))
W2:W11W2=IFERROR(IF(OR($D2="Both",$D2="P&L"),$L2/($I2-$H2)*($W$1=MEDIAN(EOMONTH($H2,-1)+1,EOMONTH($I2,0),$W$1))*(MIN($I2,EOMONTH($W$1,0))-MAX($H2,$W$1-1)),0),IF(AND(EOMONTH($H2,0)=EOMONTH($I2,0),EOMONTH($H2,0)=EOMONTH($W$1,0)),$L2,0))
X2:X11X2=IFERROR(IF(OR($D2="Both",$D2="P&L"),$L2/($I2-$H2)*($X$1=MEDIAN(EOMONTH($H2,-1)+1,EOMONTH($I2,0),$X$1))*(MIN($I2,EOMONTH($X$1,0))-MAX($H2,$X$1-1)),0),IF(AND(EOMONTH($H2,0)=EOMONTH($I2,0),EOMONTH($H2,0)=EOMONTH($X$1,0)),$L2,0))
Y2:Y11Y2=IFERROR(IF(OR($D2="Both",$D2="P&L"),$L2/($I2-$H2)*($Y$1=MEDIAN(EOMONTH($H2,-1)+1,EOMONTH($I2,0),$Y$1))*(MIN($I2,EOMONTH($Y$1,0))-MAX($H2,$Y$1-1)),0),IF(AND(EOMONTH($H2,0)=EOMONTH($I2,0),EOMONTH($H2,0)=EOMONTH($Y$1,0)),$L2,0))
Z2:Z11Z2=IFERROR(IF(OR($D2="Both",$D2="P&L"),$L2/($I2-$H2)*($Z$1=MEDIAN(EOMONTH($H2,-1)+1,EOMONTH($I2,0),$Z$1))*(MIN($I2,EOMONTH($Z$1,0))-MAX($H2,$Z$1-1)),0),IF(AND(EOMONTH($H2,0)=EOMONTH($I2,0),EOMONTH($H2,0)=EOMONTH($Z$1,0)),$L2,0))
AA2:AA11AA2=IFERROR(IF(OR($D2="Both",$D2="P&L"),$L2/($I2-$H2)*($AA$1=MEDIAN(EOMONTH($H2,-1)+1,EOMONTH($I2,0),$AA$1))*(MIN($I2,EOMONTH($AA$1,0))-MAX($H2,$AA$1-1)),0),IF(AND(EOMONTH($H2,0)=EOMONTH($I2,0),EOMONTH($H2,0)=EOMONTH($AA$1,0)),$L2,0))
AB2:AB11AB2=IFERROR(IF(OR($D2="Both",$D2="P&L"),$L2/($I2-$H2)*($AB$1=MEDIAN(EOMONTH($H2,-1)+1,EOMONTH($I2,0),$AB$1))*(MIN($I2,EOMONTH($AB$1,0))-MAX($H2,$AB$1-1)),0),IF(AND(EOMONTH($H2,0)=EOMONTH($I2,0),EOMONTH($H2,0)=EOMONTH($AB$1,0)),$L2,0))
AC2:AC11AC2=IFERROR(IF(OR($D2="Both",$D2="P&L"),$L2/($I2-$H2)*($AC$1=MEDIAN(EOMONTH($H2,-1)+1,EOMONTH($I2,0),$AC$1))*(MIN($I2,EOMONTH($AC$1,0))-MAX($H2,$AC$1-1)),0),IF(AND(EOMONTH($H2,0)=EOMONTH($I2,0),EOMONTH($H2,0)=EOMONTH($AC$1,0)),$L2,0))
AD2:AD11AD2=IFERROR(IF(OR($D2="Both",$D2="P&L"),$L2/($I2-$H2)*($AD$1=MEDIAN(EOMONTH($H2,-1)+1,EOMONTH($I2,0),$AD$1))*(MIN($I2,EOMONTH($AD$1,0))-MAX($H2,$AD$1-1)),0),IF(AND(EOMONTH($H2,0)=EOMONTH($I2,0),EOMONTH($H2,0)=EOMONTH($V$1,0)),$L2,0))
AE2:AE11AE2=IFERROR(IF(OR($D2="Both",$D2="P&L"),$L2/($I2-$H2)*($AE$1=MEDIAN(EOMONTH($H2,-1)+1,EOMONTH($I2,0),$AE$1))*(MIN($I2,EOMONTH($AE$1,0))-MAX($H2,$AE$1-1)),0),IF(AND(EOMONTH($H2,0)=EOMONTH($I2,0),EOMONTH($H2,0)=EOMONTH($V$1,0)),$L2,0))
AF2:AF11AF2=IFERROR(IF(OR($D2="Both",$D2="P&L"),$L2/($I2-$H2)*($AF$1=MEDIAN(EOMONTH($H2,-1)+1,EOMONTH($I2,0),$AF$1))*(MIN($I2,EOMONTH($AF$1,0))-MAX($H2,$AF$1-1)),0),IF(AND(EOMONTH($H2,0)=EOMONTH($I2,0),EOMONTH($H2,0)=EOMONTH($AF$1,0)),$L2,0))
AG2:AH2AG2=IFERROR(IF(OR($D$2="Both",$D$2="P&L"),$L$2/($I$2-$H$2)*($U1=MEDIAN(EOMONTH($H$2,-1)+1,EOMONTH($I$2,0),$U1))*(MIN($I$2,EOMONTH($U1,0))-MAX($H$2,$U1-1)),0),IF(AND(EOMONTH($H$2,0)=EOMONTH($I$2,0),EOMONTH($H$2,0)=EOMONTH($U1,0)),$L$2,0))



And here I'm doing the SumIf Function:

P&L Simulation - Copy.xlsm
BCDEFGHIJKLMN
2P&L OverviewJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
3Revenue0.000.000.000.008147.546147.546352.466352.460.000.000.000.00
4Costs0.000.000.00110.001932.35383.39203.28203.28196.72203.28137.700.00
5Gross Profit-110.06215.1912575764.153005
Automatic P&L
Cell Formulas
RangeFormula
C3:N3C3=SUMIF('Financial Elements'!$A:$A,"Inflow",'Financial Elements'!AB:AB)
C4:N4C4=SUMIF('Financial Elements'!$A:$A,"Outflow",'Financial Elements'!AB:AB)
F5:H5F5=F3-F4
Named Ranges
NameRefers ToCells
'Financial Elements'!_FilterDatabase='Financial Elements'!$A$1:$X$12C3:N4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C5:N5Cell Value>0textNO
F5:H5Cell Value<0textNO
F5:H5Cell Value<0textNO
C5:N5Cell Value<0textNO


The Element Type will be included too into that final table calculation.

Thank you very much for your time!
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,125
Office Version
  1. 365
Platform
  1. Windows
Cross posted
While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please supply all relevant links.
 

Sergiu G

New Member
Joined
Jun 13, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
I did not get any responses on the other threads and I successfully implemented it by myself. Now I have to convert that to VBA Code. I pointed to that thread on the other ones.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,650
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You posted on at least one other forum at roughly the same time, so you need to provide links to that (and any other) cross-post please. The rules are the same on most forums.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,013
Messages
5,545,483
Members
410,685
Latest member
chandraganji
Top