Ageing

[ryan7]

I could not find an ageing thread that answered this clearly.
I have the below dates and corresponding values listed under Amount.
I've marked the ageing bucket and what should be the correct value but I'd like to know the correct formula to save time for each.
Thanks

Date	Amount		Ageing	Totals
05/02/2021​	£ 49.96		Current	£ 1,633.91
06/04/2021​	£ 9.83		1-30 days	£ 128.98
18/05/2021​	£ 2.23		31-60 days	-£ 30.30
18/05/2021​	£ 16.06		61-90 days	0​
06/09/2021​	£ 137.15		91-120 days	£ 229.30
21/09/2021​	£ 92.15		121+ Days	£ 78.08
11/11/2021​	-£ 30.30		Total	£ 2,039.97
20/12/2021​	£ 66.96
24/12/2021​	£ 45.00
24/12/2021​	£ 1.90
31/12/2021​	£ 15.12
05/01/2022​	-£ 8.24
07/01/2022​	£ 188.69
10/01/2022​	£ 1,235.60
10/01/2022​	£ 31.21
10/01/2022​	£ 186.65

 

[ryan7]

I though you said that you wanted to do it with a Macro?

In VBA, to apply that formula somewhere, you could do it like this:

Range(...).Formula = "=SUMPRODUCT((MONTH(A$2:A$" &  lastRow & ")=MONTH(EDATE(TODAY(),-ROWS(E$2:E2)+1)))*B$2:B$" & lastRow  & ")"

I'm recording a step by step, not in VBA.

 

[Joe4]

I'm recording a step by step, not in VBA.

Recording a macro IS creating VBA (Macros and VBA really mean the same thing in Excel).

Note that you can only do real basic things with the Macro Recorder. It is very literal and NOT dynamic.
You can use it to create building blocks, and then edit the code that you created to make it more dynamic.

That is what you would have to do in this case. You cannot use Macro Recorder alone to dynamically apply the last row, like you want.