Solved

Chart not expanding when a new date is added.

Posted on 2014-03-06
10
167 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 32

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 32

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 32

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 32

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 32

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Search Workbook, then move rows to new sheet 13 31
splitting text of cell to columns 14 24
File not loading into PowerPivot 4 9
Excel callender with date slider 5 25
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

932 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

16 Experts available now in Live!

Get 1:1 Help Now