Solved

Chart not expanding when a new date is added.

Posted on 2014-03-06
10
165 Views
Last Modified: 2014-03-09
I have a defined chart area called "PltChartRange" and in a macro I insert a row to expand the chart range each time expecting that the chart will grow based on the insert.  Is there a setting that the chart needs to know it has to expand?

Even if I manually insert a row in the chart range it should expand the range right?
I attached a sample file to reivew.  Why does the chart not expand?  Also it looks like the chart is expanding vertically as the rows are added, but not horizontally to add new rows?
Something is wrong.

Quick code to add a row at row 2 to expand does not show in the chart?



Sub Macro4()
'
' Macro4 Macro
'

'
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=NOW()"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "500"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "250"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "50%"
    Range("D3").Select
End Sub

Open in new window

ChartThatAddsDates.xlsm
0
Comment
Question by:RWayneH
  • 5
  • 4
10 Comments
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39910911
For starters, you can simplify that code a little:

Rows("2:2").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A2").FormulaR1C1 = "=NOW()"
    Range("B2").FormulaR1C1 = "500"
    Range("C2").FormulaR1C1 = "250"
    Range("D2").FormulaR1C1 = "50%"
    Range("D3").Select
End Sub

Open in new window

However, that as you rightly point out does not add to the graph, are you expecting the new row to become a new series in the graph?

Where does current graph range start? If the range starts at row 2, by inserting a row at row 2 you are just pushing the grah data range down one row. I assume row 1 is headers and is included in the data range.

Rob H
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39910928
Just looked at the file and I suspect the graph has been setup thus:

Data selected rows 2 to 7
Series edited to add headers from row 1

This chart data range is row 1 and rows 2 to 7 rather than rows 1 to 7.

Macro inserts at row 2 pushing it down as suggested earlier.

Select graph and use Select Data option to select A1 to D13 and then remove series created for columns B & C.

Thanks
Rob
0
 

Author Comment

by:RWayneH
ID: 39910934
You are correct, I am just shifting the data down.  The chart range is in the dropdown above cell A1 called "PltChartRange".  Is there something else that needs to be done to the chart in order to grab the new stuff?
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 39910972
Inserting a row above the chart series range does not include that line.
Also inserting an entire row changes the size of the chart, which I don't think is intended.
Using Now as a formula for the date will change the value tomorrow, so not good.

I guess you want to insert cells from column A to F for a new days values.
Insert todays date in A3, and a formula to calculate the percentage in cell D3.
Fill values in B3 and C3 manually.

Here is an example, Press the button to run.

Have inserted a row with empty cells (row 2) as the start of the chart series.
So when inserting in row 3 it will be included in the chart series range.
Row 2 is hidden.
Macro inserts range A3:F3, but only when the date in A3 is before today, so you can not insert twice on the same day.
The formula in D3 calculates C3/B3 when possible, else the value NA (Not Available) to make the chart display correctly.
Conditional formatting in column D, so when value is NA, text colour is white, to make it invisible.

The code is this
Sub NewDateLine()
    If Range("A3") < DateSerial(Year(Now), Month(Now), Day(Now)) Then
        Range("A3:F3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("A3") = DateSerial(Year(Now), Month(Now), Day(Now))
        Range("D3").Formula = "=IF(AND(B3>0,C3>0),C3/B3,NA())"
        Range("B3").Select
    End If
End Sub

Open in new window

ChartThatAddsDates1.xlsm
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 39910980
You may have the PltChartRange setup but the chart isn't referring to it!!

I have just tried resetting the graph to that range by setting data range to:

='MOT-MeasureData'!PltChartRange

and it added two extra series for Bruce Total and Orders Completed.

I then went back into the Select Data window and used the button to remove the extra series and the data range above changed to:

='MOT-MeasureData'!$A$1:$A$13,'MOT-MeasureData'!$D$1:$D$13

As I said previously, the original had a data range in a similar format but had row 1 and rows 7 to 13 rather than 1 to 13:

='MOT-MeasureData'!$A$1,'MOT-MeasureData'!$A$7:$A$13,'MOT-MeasureData'!$D$1,'MOT-MeasureData'!$D$7:$D$13

You have a couple of options:
1) Change your definition of PltChartRange to:

='MOT-MeasureData'!$A$1:$A$13,'MOT-MeasureData'!$D$1:$D$13

and then use Select Data window to set chart range to:

='MOT-MeasureData'!PltChartRange

2) Simply set the chart range to:
='MOT-MeasureData'!$A$1:$A$13,'MOT-MeasureData'!$D$1:$D$13

As you add rows at row 2, the new data will be included in the range in either scenarion because you ahve a continuous range rather than split.

Thanks
Rob
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 31

Expert Comment

by:Rob Henson
ID: 39910990
As I mentioned earlier, I suspect the split range was caused by the way the graph was created in the first place; either  the data was selected and then the headers added or the ranges were selected using Ctrl and mouse clicks rather than Shift and clicks.

Thanks
Rob
0
 

Author Comment

by:RWayneH
ID: 39912077
Ok I believe I understand now how this works, as a maintenance question and as the chart gets huge, how do I limit the data to a month or a number that fits on the chart nicely,  I thought about adding a Average to column E at the bottom of the range add a formula the would include the new day in the average, then copy that up to the new row.  If I average a named column range, will that expand now too?
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39912091
As a simple fix, if you hide rows they won't appear on the chart.

Thanks
Rob H
0
 

Author Comment

by:RWayneH
ID: 39912549
and to add the average?  If I put the average in column E, and named that column MSC_Average.  Could I use =AVERAGE(MSC_Average) and as the chart range increases so would the MSC_Average named range?

I usually will do a copy, special paste to remove any formulas at the end.  Just want to be sure that I am thinking correctly on the approach to the chart.
0
 

Author Closing Comment

by:RWayneH
ID: 39916159
Thanks for the help, understanding the chart, settings. -R-
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now