Calculating a Dynamic Range of Data in Excel

Bright01
Bright01 used Ask the Experts™
on
I have a Worksheet that EE has helped me with that needs "adjustment".

What I'm trying to accomplish:   I am trying to have the model automatically expand (by years up to 10) by changing a cell (D3) and automatically cut/paste the cells and formulas in the first column (Column E5-E9) and place that data in any column that has a number in E4 (based on the change).  If the number in D3 is changed, then the model adapts to the change by deleting or adding data in the appropriate columns.  This is simply a dynamically expanding array.

You will see in the sample I have attached that presently the model doesn't work accurately; with the formulas "hardcoded" into the macro itself, they are not easy to change. In the example provided, the numbers are "Summed" rather then subtracted due to the hardcode.  If the formula was correct in cells E7 and E9, then you would see the right answers....  That is why I need a simpler, cut/paste approach.

Thank you in advance,

B.
Calculating-a-Dynamic-Range-in-E.xlsm
Comment
Watch Question

Do more with

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

Commented:
What are the correct formulas in E5-E9 as you would usually enter them on the worksheet?

I've looked at the code to try and work that out but you seem to have overcomplicated things a bit.

You definitely don't need an array to get the column letter, in fact you don't need the column letter if you use R1C1 notation for the formulas.

For example the first formula:

Cells(7, count1).FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"

And I think for the 2nd formula (=E7-E8 for 2011)

Cells(9, count1).FormulaR1C1 = "=R[-2]C-R[-1]C"

We can also use something like  this to enter the years across.
Dim NoYrs As Long

NoYrs = Range("D3")

With Range("E4")
    .Value = 2011
    .Resize(, NoYrs).DataSeries Rowcol:=xlRows, Type:=xlChronological, Date:=xlDay, Step:=1, Trend:=False
End With

Open in new window

NorieAnalyst Assistant

Commented:
Here's all of the above tied together.

Remember for the formulas I'm going on what I've been able to
work out from your code so they may very well be completely wrong.
Dim NoYrs As Long
    NoYrs = Range("D3")

    With Range("E4")
        .Value = 2011
        .Resize(, NoYrs).DataSeries Rowcol:=xlRows, Type:=xlChronological, Date:=xlDay, Step:=1, Trend:=False
    End With

    Range("E7").Resize(, NoYrs).FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"


    Range("E9").Resize(, NoYrs).FormulaR1C1 = "=R[-2]C-R[-1]C"

Open in new window

Author

Commented:
Imnorie,

Thank you for the quick reply.  I agree that your simpler approach is far better.  However, I'm not looking to embed the formulas in the generated columns in the Macro.  I want to have the macro copy the appropriate cells in the column (E) to the right number of selected YEARS.  That way I can change or adjust the formulas in the first column and it will be replicated outside the macro.

Make sense?

B.
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

NorieAnalyst Assistant

Commented:
If you have the correct formulas in E7 and E9 then it's even easier.

NoYears = Range("D3").Value

Range("E7").Copy Range("E7".Resize(, NoYears)

Range("E9").Copy Range("E9").Resize(, NoYears)

Author

Commented:
imnorie,

Brilliant!  Can you put it in the sample I sent and send back the package?  Much appreciate your help on this.  Let me try it out.

B.
NorieAnalyst Assistant

Commented:
Here it is.

I've put the code in the change event.

There's no error checking but it can be added, and perhaps more importanly code can be added to remove
the existing formulas, apart from E7 and E9 of course, before creating the new ones.
Calculating-a-Dynamic-Range-in-E.xlsm

Author

Commented:
imnorie,

This is not at all right.  When you enter a number in D3 the model should expand or contract to that number and add in the Year in Row 4 corresponding with the number in D3.  (Example:  If I enter 5 in D3, and the year in cell E4 is 2011, then in E5 = 2012, E6=2013, E7=2014, E8=2015.  If I change it to 3 (in D3) then cell E4 is 2011, cell E5 is 2012 and cell E6 is 2013. The data in E7 and E8 vanish.

Next, the data and formulas in cells E5-E9 are copy and pasted into the cells under each year.

That's how it's suppose to work.

Make sense?

B.
NorieAnalyst Assistant

Commented:
You haven't been very clear what you want to do, so I've kind of been guessing here.

Are you saying that's what the code is doing or what it's meant to do?

If all you really want to do is copy everything in E5:E9 across try this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NoYrs As Long

    Application.EnableEvents = False
    
    NoYrs = Range("D3")

    With Range("E4")
        .Resize(, NoYrs).DataSeries Rowcol:=xlRows, Type:=xlChronological, Date:=xlDay, Step:=1, Trend:=False
    End With
    Range("E5:E9").Copy Range("E5:E9").Resize(, NoYrs)


    Application.EnableEvents = True
    
End Sub

Open in new window

Author

Commented:
Imnorie,

Sorry for not being clear.  IF you look at the original xls., you will see what I'm trying to do;

The number you put into D4 expands or contracts the headings for the YEAR.  All I wanted was for the cells that are between E5-E9 be copied into those sells that have Headings (and deleting them if the number in D4 is changed to a smaller number).  This way the formulas can be easily changed and not hardcoded into the Macro.

The Macro you sent me doesn't expand or contract the Years.  I'm not sure what it's doing.

Thank you,

B.
NorieAnalyst Assistant

Commented:
I said earlier 'code can be added to remove the existing formulas'.

Author

Commented:
What?  Now I'm confused....where are we on this?

B
Analyst Assistant
Commented:
I think this is it.

It takes the no of years from D3 and taking E4 as the start year fills across in row 4 based on no of years.

It then copies cells E5:E9 across the same no of columns.

Before it does any of that it clears rows 5 to 9 from column F onwards, removing any existing formulas
EE-Calculating-a-Dynamic-Range-i.xlsm

Author

Commented:
Imnorie,

Thank you very much!   I was working on it this morning when I got your update.  You saved me a lot of time.  My trials were not yielding anything close to the right result.  Yours did.  

Much thanks,

B.
NorieAnalyst Assistant

Commented:
No problem, sorry for the earlier confusion.:)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial