[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Chart not expanding when a new date is added.

Posted on 2014-03-06
10
Medium Priority
?
181 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 33

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 33

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

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 33

Accepted Solution

by:
Rob Henson earned 2000 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
 
LVL 33

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 33

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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 …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

650 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