Trouble using LOOPS and OFFSETS in a financial scenario model

[ExcelUser321]

Dear MrExcel Forum Users

I’m a beginner to VBA and am attempting my first manually created macro. After 2 days of chipping away- online courses, reading other posts, my own attempts- I’ve hit a wall and am seeking your guidance.

I wish to automate a repetitive process that I could implement by recording a macro. But I want to learn how to use VBA code to create an elegant and simple solution.

Software used:

• Excel version 2010
• VBA version 7.0
• Windows version 7 Professional

This is a description of what I’m attempting to achieve:
For each year (Years 1 to 5), to record the net cash flow for each change in 2 variables (Selling price and Number of Customers). Each of these two variables have 6 points namely:

• Selling price = From £16,000 to £6,000 (in increments of £2,000)
• Initial number of customers in Yr1 = From 200 to 50 (in increments of 30)

The net cash flow is formula-driven based on the figures inserted into the Selling Price (L3) and Number of Customers (L4) cells.

The role of the macro isn’t to calculate net cash flow, merely to take the net cash flow output per Cells D10:H10 (Years 1 to 5 respectively) after changing one of the 2 variables and pasting them into the relevant years’ Selling price:Customer matrix (table).

The Selling price:Customer matrix is identical for each of the 5 years and is in the following format on the worksheet:

• D15:I16; D25:I25 = Selling price in ascending order for years 1 and 2 respectively, as an example
• B16:B21; B36:B41 = Number of customers in ascending order for years 1 and 2 respectively, as an example

All figures used by the macro reside on the same worksheet.

This is a description of where I’m struggling:
I’ve managed to populate the Selling Price:Customer tables for all 5 years, for the one scenario (Selling price of £16K and 200 customers). I’m now stuck on cycling through the remaining 35 scenarios and populating the 5 tables with the net cash flow output.

This is my code:
-------------------------------------------------------

Sub Cash_Flow_Matrix()

'This macro populates the net cash flow matrix for a given combination of: average selling price (£) and number of customers

'Selling price (aka Average Price Point/APP) starting cell reference
Dim APP_initial As Range
Set APP_initial = Range("I15")

'Defining the Customer numbers starting cell reference
Dim Cust_initial As Range
Set Cust_initial = Range("B16")

'Defining the cell reference for the APP input
Dim APP_input As Range
Set APP_input = Range("L3")

'Defining the cell reference for the Customer number input
Dim Cust_input As Range
Set Cust_input = Range("L4")

'Defining starting cell reference for Year 1 Net Cash Flow result
Dim YR1NCF_result As Range
Set YR1NCF_result = Range("D10")

'Defining starting cell reference for populating the Net Cash Flow matrix equal to Year 1 - £16K - 200. All other Years’ inputs will be based on an offset of this starting range
Dim YR1Matrix_input As Range
Set YR1Matrix_input = Range("I16")

'Step 1- Copy+paste special the initial variables into the model
‘Insert Starting APP value into the model
APP_initial.Copy
APP_input.PasteSpecial Paste:=xlPasteValues

'Insert Starting Customer numbers into the model
Cust_initial.Copy
Cust_input.PasteSpecial Paste:=xlPasteValues

'Step 2 - Copy+paste special annual net cash flow into the matrix for a given APP
'2.1: Year 1 - Initial value inserted into matrix

YR1NCF_result.Copy
YR1Matrix_input.PasteSpecial Paste:=xlPasteValues

'Defining number of loops for populating across 5 years
Dim Num20 As Integer
Num20 = 4

‘Updating all 5 years’ Selling price : Customer matrices for the first scenario of 36 scenarios
Do

Set YR1NCF_result = YR1NCF_result.Offset(0, 1)
Set YR1Matrix_input = YR1Matrix_input.Offset(10, 0)

APP_initial.Copy
APP_input.PasteSpecial Paste:=xlPasteValues

YR1NCF_result.Copy
YR1Matrix_input.PasteSpecial Paste:=xlPasteValues

Num20 = Num20 - 1
Loop Until Num20 = 0

'Remove any active focus on cells
Application.CutCopyMode = False

End Sub
-------------------------------------------------------


I hope that this message is detailed enough but am happy to provide any additional info that will assist you in assisting me.

Many thanks, ExcelUser321