Thread: Graphs with differnet amounts of data

Hi,

I have tried follow the guidlines on the website http://peltiertech.com/Excel/Charts/Dynamics.html under the Chart the Last 12 Months Dynamically part.

However it hasnt quite worked for me - i have attached a copy of what i have done. I think i can see what has gone wrong, it is looking upwards for data, rather than downwards but i do not know how to fix it

Any help would be appreciated

Thanks

Antony

Hi,

thanks for that.

I have tried to write a macro to build the graph automatcially aswell

   Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("data").Range("F5")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = "=offset chart.xls'!chtCats"
    ActiveChart.SeriesCollection(1).Values = "=offset chart.xls'!chtValA"
    ActiveChart.SeriesCollection(1).Name = "=data!R4C2"
    ActiveChart.SeriesCollection(2).Values = "=offset chart.xls'!chtValB"
    ActiveChart.SeriesCollection(2).Name = "=data!R4C3"
    ActiveChart.Location Where:=xlLocationAsNewSheet

however i am unsure as to why it needs to pull the name of the xls, as i need this to be dynamic as every day i would like to be able to run the macro to create the graph.

Any ideas on how i get round this?

Thanks

Antony

Hi Andy,

thanks for the above.

I have also macroed to write the defined names, however it puts the " " at the begining and end of the wording and therefore doesnt like it - is there a way around this?

Thanks

Antony

 ActiveWorkbook.Names.Add Name:="chtLen", RefersToR1C1:="data!R5C4"
    ActiveWorkbook.Names.Add Name:="chtCats", RefersToR1C1:= _
        "OFFSET(data!$A$5,0,0,MIN(chtLen,COUNTA(data!$A:$A)-1),1)"
    ActiveWorkbook.Names.Add Name:="chtValA", RefersToR1C1:= _
        "OFFSET(chtCats,0,1)"
    ActiveWorkbook.Names.Add Name:="chtValB", RefersToR1C1:= _
        "OFFSET(chtCats,0,2)"

Hi,

I have done all of the above, and so at the back end of the macro i have the below. However it bombs out at the XValues bit. If i run the other part of my macro without the below, then run the below bit separately it works - it is a right head scratcher.

Let me know if you need any further info

Antony

 
Charts.Add
    With ActiveChart
        .ChartType = xlColumnClustered
        With .SeriesCollection.NewSeries
            .XValues = "='" & ThisWorkbook.Name & "'!chtCats"
            .Values = "='" & ThisWorkbook.Name & "'!chtValA"
            .Name = "=data!R4C2"
        End With
        With .SeriesCollection.NewSeries
            .Values = "='" & ThisWorkbook.Name & "'!chtValB"
            .Name = "=data!R4C3"
        End With
    End With
    
End Sub

HI andy,

it works for me fine as two parts, but not as a whole. I cant down load anything at work, so will have to check tonight

Antony

Hi Andy,

I think I may has spotted the issue - I agree the macro works fine in your work book and if I copy it into another workbook it works ok as well, the issue comes if I try and run the Marco whilst it is one workbook against data in another work book, which is going to cause me an issue as I would ideally like to save this Marco in my personal, as I am going to need to be able to run it daily.

Any ideas????

Thanks

Antony