Macro to copy/paste input, run calculation and the copy/paste outputs

[RccScln]

Hello all
I am a novice in the use of excel macros. I would need help to automate some easy actions such as substituting within a financial model calculation certain inputs systematically - let's say n scenarios (input scenario 1, in put scenario 2 input scenario3 n=number of scenarios) and have the model calculate them and copy and paste the calculated outputs into a recap table.
I attache below a minisheet hoping is helpful.
Thank you in advance

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2		Input model working
3		15.2
4		5%							Reacp table
5		12%
6		5							Input			SCENARIO 1	SCENARIO 2	SCENARIO 3
7									AAA	15.2		10	25	5
8			31/12/2024	01/01/2025	02/01/2026	03/01/2027			BBB	5%		4%	3%	6%
9		NCF	-800	300	200	550			CCC	10%		11%	15%	22%
10									DDD	5		4	3	1
11	Outptus generated with calcl
12	IRR	27%
13	MOIC	1.3							Output			SCENARIO 1	SCENARIO 2	SCENARIO 3
14	WAL	5							IRR	27%		0%	15%	0%
15	PP	116							WAL	5.0		5.0	5.0	5.0
16									MOIC	5.0		0.0	0.0	0.0
17									PP
18
Sheet1

Cell Formulas
Range	Formula
B12	B12	=+XIRR(C9:F9,C8:F8)
B13	B13	=-SUM(D9:F9)/C9
J14,L16:N16,J16,N14,L14	J14	=+B12
B15	B15	=+XNPV(B5,C9:F9,C8:F8)

 

[offthelip]

It is not at all clear as to which cells you want copied and pasted either before or after the calculation so I made some guesses but this code show you how to do it:

Sub test()
For i = 1 To 3
  inarr = Range(Cells(7, 11 + i), Cells(10, 11 + i))   ' pick L7:L10, M7:M10, and N7:N10 in turn
  Range("j7:j10") = inarr    ' paste into J7:J10
  Application.Calculate
  outarr = Range("j14:j16") ' pick up the output
  Range(Cells(14, 11 + i), Cells(16, 11 + i)) = outarr ' Paste into  L14:L16, M14:M16, and N14:N16 in turn
 Next i

End Sub

 

[RccScln]

Sorry offthelip, I will try to make it clearer. I want to copy cells L7:L10 to B3:B6, run calculation that will affect outputs in B12:B15, then copy these outputs in recap table L13:L16; then again I want to copy cells M7:M10 to B3:B6, run calculation that will affect outputs in B12:B15, then copy these outputs in recap table M13:M16. And then again I hope it clear.
But I think your macro should be able to work for my purposte.
Last question: to write a macro what should I do? View / Macros / Record and then copy paste your code is the best?

Last last question: how can i support you with a coffee?
Thanks

 

[offthelip]

I have altered the code to do the copying just as you describe:

Sub test()
For i = 1 To 3
  inarr = Range(Cells(7, 11 + i), Cells(10, 11 + i))   ' pick L7:L10, M7:M10, and N7:N10 in turn
  Range("B3:B6") = inarr    ' paste into B3:b6
  Application.Calculate
  outarr = Range("B12:B15") ' pick up the output
  Range(Cells(13, 11 + i), Cells(16, 11 + i)) = outarr ' Paste into  L13:L16, M13:M16, and N13:N16 in turn
 Next i

End Sub

I am not the best person to advise on how to start with macros since I know how I do it but there are lots of ways.
I always have the developer tab in view, which you get from Excel settings, customise ribbon , then on the developer tab select visual basic and in the window that appears select the workbook, right click and insert module, and then paste the code in the module. However this may not be the easiest way of doing this.

 

[RccScln]

Thank you very much. What if the recap table and input are in another sheet different from the out where the working model input works? Shall I add the sheet number in this portion of code?

Range("B3:B6") = inarr ' paste into B3:b6

Thanks

 

[offthelip]

Yes you can the best way to do it is using the "with" statement like this:

Sub test()
For i = 1 To 3
With Worksheets("Sheet2")
  inarr = .Range(.Cells(7, 11 + i), .Cells(10, 11 + i))   ' pick L7:L10, M7:M10, and N7:N10 in turn
  .Range("B3:B6") = inarr    ' paste into B3:b6
End With
  Application.Calculate
  outarr = Range("B12:B15") ' pick up the output
  Range(Cells(13, 11 + i), Cells(16, 11 + i)) = outarr ' Paste into  L13:L16, M13:M16, and N13:N16 in turn
 Next i

End Sub

this avoids swapping the active cheet to and from the input and the outputs sheets. You can use the with statement construct for both which probably the best way because then the macro will run corrctly regardless of which sheet is active.
Note the addition of the dots in the addressing this is very important