We help IT Professionals succeed at work.

Macro for auto incrementing a Model Horizon

Bright01
Bright01 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
this is will work more correct way
Sample-Expansion-of-Model-Horizo.xlsm

Commented:
Above example is working on  worksheet change, you need to enter on D3 any value example 2 then click on some other cell or Press enter then it will work
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

Open in new window

Author

Commented:
Ukerandi,

Thank you for the quick response.  The dates work but it doesn't copy the cells (or we could do it by range).   So take a look at cells E7 and E9..... they should be reflected in any change in columns (additions or retractions).  That's the only thing I see missing.

B.

Commented:
ok sorry check this one
Sample-Expansion-of-Model-Horizo.xlsm

Author

Commented:
ukerandi,

Thanks for the quick reply!  We're almost there.  Great work.

Several small items here; the year should not be hard coded; it should key off of cell E4 (e.g. 2011 or 2012 or 2013, etc.) then extend out the correct number of years.  Also, 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.  Lastly, can you tell me how you got the formulas to show up in the cells without exposing them?  If I want to add new formulas, I have to know where to go or how it was done.

Much thanks in advance.

B.

Commented:
yes you need to edit near below code or send me email praera2000atyahoodotcom

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.

Author

Commented:
ukerandi,

I understand.  The date (in E5) and the formulas (e.g. Cells(8, count1)=Cells(5,count1)......) have been hard coded into the macro.  This is not what I asked for.  I asked that the formulas in the first column (E) were copied and pasted.  The reason for this was so I could change the formulas and they would work across the additional fields.  At this point, I cannot use this macro or sheet.  Again, the macro needed to expand based on a number 1-10 and create the appropriate number of new columns or if reduced, delete those columns that were > then the new number (of years).  The other point was that the formulas (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).  At the present time, the macro doesn't do that in a manner that can be changed.

Please help.

B.

Author

Commented:
Actually, I think the easy fix for this is a copy paste function/subroutine so that the formulas (only) in column E can be pasted and deleted based on the number of columns expanded or contracted.  It may be that simple.............

Author

Commented:
And all that has to happen in the date field is that you need to be able to put in a Year (e.g. 2011, 2010, 2012, etc.) and that's the starting year with increments of "N".

Author

Commented:
If it's easier, then you can copy all the fields in the first column and then the user can edit them (including the numbers) ....

Commented:
Can you explaing
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

Author

Commented:
Yes.

The idea here was to create a way to expand or contract a set of columns that included formulas in the first column.  The problem with hard coding it is that I cannot change the formulas that need to be in each column under each year.  That's why I said, "the 6 rows".  

I also need to be able to put in any  year, 2010/11/12 and have it increment by 1 for the appropriate number based on the selection (i.e. 2, 3, 4, etc.).

By being able to expand or contract the columns (with clearing), the model becomes both dynamic and scalable/changable.

Does that make sense?

B.

Commented:
check this
Final.xlsm

Commented:
this one working fine
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

Author

Commented:
Ukerandi,

Unfortunately, you're not getting the design/requirement for copy/paste on the formulas in Column E.

You've added a button to perform the macro instead of having the input/change in cell E4 trigger the macro.  You have still put the calculation formula in the macro instead of the copy and paste option suggested and required in order to change the formula.   I would love to say this has been nailed but I don't think you understand what I'm trying to do.

B.  

Commented:
Did you mean like this,

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

Author

Commented:
Ukerandi,

In order to meet the original request;
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.
2.) the formulas in cells in E5 - E9 need to be copied or deleted based on the input to cell D3.  
3.) When putting in a lower number (i.e. 3 after 5 in D3), the columns need to be erased.

I'm trying to create a model that can adapt to changing formulas and adapt to the right number of years of consideration.

Thank you,

B.

Author

Commented:
BTW; the macro should recognize the number in cell E4 and not require a button to update.

B.

Commented:
i finished this below parts
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

Commented:
Hopefully this one is final answer. see file
Sample-Expansion-of-Model-Horizo.xlsm

Author

Commented:
Ukerandi,

You are a lot closer on this.  However, put 8 in the number of years..... you will see an error.  Second, can you tell me how the formulas are calculated in the added cells?  If I want to change the formulas, how do I do that?  With cut and paste, I could change the formula in the cell(s) and it would be replicated.

We're close.

Thank you,

B.
Commented:
If I want to change the formulas, how do I do that?  With cut and paste, I could change the formula in the cell(s) and it would be replicated.

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




 

Author

Commented:
Good work; however, I received half of what I needed.  The column extension worked great but instead of copying the formulas from the first column and inserting them based on the number of columns displayed, the formulas are now hardwired into the macro itself.  I'll need to do more work on this and ask a related question to find a fix to this.  I do appreciate the work and effort.

Thank you,

B.

Commented:
befoer gross profit are u going to enter more rows, For example before Expected Costs are you going to enter some more revenues and do u  need to add more expenditure

for example before Gorss profit
other than expected profit do u need to add another incomes rows?