EE Professionals,

I'm looking for a very simple Macro that when a number between 1-10 is presented, it automatically takes the current year and adds +1 to it automatically adds (not populates) that number of columns so as to provide an area for input. Sample attached. In the sample, the number 5 represents the number of years for "analysis". By putting in "5" the model needs to expand by copy and input of 6 additional rows, in 5 columns titled "2012", "2013", etc. The reason for the copy/insert is so formulas that are in the column before the date, and date expansion will be copied into the new columns.

Much thanks,

B.

Sample-Expansion-of-Model-Horizo.xlsm

I'm looking for a very simple Macro that when a number between 1-10 is presented, it automatically takes the current year and adds +1 to it automatically adds (not populates) that number of columns so as to provide an area for input. Sample attached. In the sample, the number 5 represents the number of years for "analysis". By putting in "5" the model needs to expand by copy and input of 6 additional rows, in 5 columns titled "2012", "2013", etc. The reason for the copy/insert is so formulas that are in the column before the date, and date expansion will be copied into the new columns.

Much thanks,

B.

Sample-Expansion-of-Model-Horizo.xlsm

Do more with

EXPERT OFFICE^{®} is a registered trademark of EXPERTS EXCHANGE^{®}

Worksheet_SelectionChange event

you can put below code any where u want

```
Dim cout As Double
Dim count1 As Double
Dim year1 As Double
Count = Cells(3, 4)
year1 = 2011
count1 = 5
While (Count >= 1)
Cells(4, count1) = year1
year1 = year1 + 1
Count = Count - 1
count1 = count1 + 1
Wend
Dim coltemp As Integer
Dim RowTemp As Integer
coltemp = count1
RowTemp = Cells(3, 4)
RowTemp = RowTemp + 1
While (RowTemp < 10)
Cells(4, coltemp) = ""
coltemp = coltemp + 1
RowTemp = RowTemp + 1
Wend
```

this formula fields

Cells(7, count1) = Cells(5, count1) - Cells(6, count1) //subtracting for example E5-E6 then results come to E7

Cells(9, count1) = Cells(7, count1) - Cells(8, count1)//subtracting for example E7-E8 then results come to E9

and you need to go Macro and Click on Edit button then you can see the coding.

see additional request; "up to 6 additional rows") needed to be copied from the original column E to the new number of columns required (i.e. 1-10). if you can give em example

when changing the # of years from a higher number (say 5) to a lower number (say 3), all numbers in col/rows for 4 and 5 should be cleared

if you can show me out put example how ure are expecting ,may be i can do better way.

Sample-Expansion-of-Model-Horizo.xlsm

1.) When you put in a number (e.g. 2010, 2011, 2012, etc.) in Cell E4, that number is incremented based on +1 based on the requirement from Cell D3

3.) When putting in a lower number (i.e. 3 after 5 in D3), the columns need to be erased.

Sample-Expansion-of-Model-Horizo.xlsm

if you need to change formaulas you need to go to Macro

for example You need to ADD E5 and E6 numbers,you need to use SUM formular function in Excel so you need to use like that

=SUM(E5:E6) ,for that you need to go Macro and need to find out

Cells(7, count1).Formula = "=Erandi(" & arr1(arrcount) & "5" & "," & arr1(arrcount) & "6" & ")"

and Changed it to

Cells(7, count1).Formula = "=SUM(" & arr1(arrcount) & "5" & ":" & arr1(arrcount) & "6" & ")"

like that you and use any formula ,hope above will help you to understand

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial