CODEWITHSUNDEEP
excelchartsvba
Mike
Mike

Reputation: 1938

How to update the values or range for a graph with multiple series?

I have a spreadsheet that has several charts. I house the data for the charts on 3 other sheets. I am trying, so far without success, to update the charts to cover additional data as I add it.

I have tried several things based on what I've seen around on the web. I've tried setting a range like 

Set SourceRange1 = Split(Join((GetArray(Sheets(1).Range("P6:" & GetLetterFromNumber(Sheets(1).Cells(6, Sheets(1).Columns.Count).End(xlToLeft).Column) & "6"))), "|"), "|")
Set SourceRange2 = GetArray(Sheets(2).Range("P6:" & GetLetterFromNumber(Sheets(2).Cells(6, Sheets(2).Columns.Count).End(xlToLeft).Column) & "6")))
Set SourceRange3 = Application.Union(Worksheets(3).Range("P5", Sheets(3).Cells(5, Sheets(1).Columns.Count).End(xlToLeft)), Sheets(3).Range("P6", Sheets(3).Cells(6, Sheets(3).Columns.Count).End(xlToLeft))).Value

and then assigning it to the chart like this:

ActiveChart.SeriesCollection(1).Values = SourceRange1
ActiveChart.SeriesCollection(2).Values = SourceRange2
ActiveChart.SeriesCollection(3).Values = SourceRange3

This gives me the range P5:x5 currently, but it doesn't set the chart series.

Just for reference, the GetArray is a function that I created:

Function GetArray(Range1 As Range)
    Dim Test() As String, cell As Range, i As Integer
    i = 0
    ReDim Test(0)
    For Each cell In Range1
        ReDim Preserve Test(i)
        Test(i) = cell.Value
        i = i + 1
    Next cell
    GetArray = Test
End Function

And so it the GetLetterFromNumber:

Function GetLetterFromNumber(Number)
    GetLetterFromNumber = Split(Cells(1, Number).Address(True, False), "$")(0)
End Function

With the current setup like the Set SourceRange1, I get a

Compile error: Can't assign to array

I get the same error with both of the others as well. I have SourceRange1-3 setup like Dim SourceRange1()

If I set them up like Dim SourceRange1 then I get:

Run-time error '13':

Type Mismatch On all three.

If I assign the SeriesCollection directly like:

ActiveChart.SeriesCollection(1).Values = GetArray(Sheets(1).Range("P6:" & GetLetterFromNumber(Sheets(1).Cells(6, Sheets(1).Columns.Count).End(xlToLeft).Column) & "6"))
ActiveChart.SeriesCollection(2).Values = GetArray(Sheets(2).Range("P6:" & GetLetterFromNumber(Sheets(2).Cells(6, Sheets(2).Columns.Count).End(xlToLeft).Column) & "6"))
ActiveChart.SeriesCollection(3).Values = GetArray(Sheets(3).Range("P6:" & GetLetterFromNumber(Sheets(3).Cells(6, Sheets(3).Columns.Count).End(xlToLeft).Column) & "6"))

The it assigns 0 to all the values when for SeriesCollection(1) they should be:

(20,30,40,50,60,10,20,30,40)

I'm sure I'm missing something simple, I just don't know what. If I've missed putting something in my description let me know and I can add it.

Otherwise, all the help I can get will be appreciated.

Upvotes: 0

Views: 1413

Answers (1)

Shai Rado
Shai Rado

Reputation: 33692

In order for the Chart.SeriesCollection(1).Values to be connected dynamically with the Range use something like the line below (assuming your SourceRange1 range is defined correclty):

ActiveChart.SeriesCollection(1).Values = "=" & SourceRange1.Address(True, True, xlA1, xlExternal)

When working with Charts you might also consider using:

Dim Cht as Chart
Dim Ser as Series
Set Cht = ActiveChart 
Set Ser = Cht.SeriesCollection(1)

With Ser
    .Values = "=" & SourceRange1.Address(True, True, xlA1, xlExternal)
End With

Upvotes: 1

Related Questions