Using Cells() for creating charts? (german Excel 2003) RRS feed

  • Question

  • Hi,

    actually my code looks likeActiveChart.SetSourceData

    Source:=Sheets(Verzeichnis).Range("A1,C1,E1,G1,A1:A32,C1:C32,E1:E32,G1:G32"), _

    Working with this is a pain, i want to "translate" this into Cells
    A1=Cells(1,1) and so on.

    Can someone tell me why the following code didn`t work? (the only value the new chart get is =temp!$A$1 )

    ActiveChart.SetSourceData Source:=Sheets(Verzeichnis).Union(Range(Cells(1, 1), Cells(32, 1)), Range(Cells(1, 3), Cells(32, 3)), Range(Cells(1, 5), Cells(32, 5)), Range(Cells(1, 7), Cells(32, 7))), _


    Thursday, October 11, 2012 11:02 AM

All replies

  • You have to make all occurrences of Range and Cells refer to the Verzeichnis sheet:

    With Sheets(Verzeichnis)
        ActiveChart.SetSourceData _ 
            Source:=Union(.Range(.Cells(1, 1), .Cells(32, 1)), _
            .Range(.Cells(1, 3), .Cells(32, 3)), _
            .Range(.Cells(1, 5), .Cells(32, 5)), _
            .Range(.Cells(1, 7), .Cells(32, 7))), _
    End With

    Regards, Hans Vogelaar

    Thursday, October 11, 2012 11:12 AM
  • Thanks

    I had to exchange the x and y axis and the problem changed....

    ActiveSheet.Name = Verzeichnis
         Dim Diagrammzeilen As String
      r = 1
    While r < Email + Email
         If r <> 1 Then
         Diagrammzeilen = Diagrammzeilen & ","
         End If
         Diagrammzeilen = Diagrammzeilen & r & ":" & r
         r = r + 2
    Diagrammzeilen = "" & Diagrammzeilen & ""
    Debug.Print Diagrammzeilen
    ' Diagramm Makro
    ' Makro am 11.10.2012 von XYZ aufgezeichnet
        ActiveChart.ChartType = xlLineMarkers 
        ActiveChart.SetSourceData Source:=Sheets(Verzeichnis).Range(Diagrammzeilen), _
        'ActiveChart.SetSourceData Source:=Sheets("temp").Range("1:1,3:3,5:5,7:7"), _

    The debug print of Diagrammzeilen is:


    Whats Wrong with my code?

    Same Problem as mentioned above...

    Thursday, October 11, 2012 2:07 PM
  • Not sure why you are changing the code Hans suggested, and making a multiarea address of entire columns. However it's your address length greater than 255 that fails.

    If you want to plot up to 255 series adapt something like this

    Sub test2()
    Dim i As Long
    Dim sName As String
    Dim rngData As Range
    Dim wsData As Worksheet
    Dim wsChart As Worksheet
    Dim cht As Chart
    Dim sr As Series
    Dim dataFirst As Long, dataLast As Long
    Dim cnt As Long
         Set wsData = Worksheets("Sheet2")
         Set wsChart = Worksheets("Sheet1")
         With wsChart
                 Set cht = .ChartObjects.Add(.Range("B2").Left, _
                                                                         .Range("B2").Top, 600, 300).Chart
         End With
         cht.ChartType = xlLineMarkers
         For i = 1 To 9 Step 2
                 cnt = cnt + 1
                 With wsData
                         .Cells(1, i) = "data " & cnt
                         Set rngData = .Range(.Cells(2, i), .Cells(32, i))
                         rngData.Formula = "=INT(RAND()*100)"
                         sName = Application.ConvertFormula(.Cells(1, i).Address, _
                                                                                                xlA1, xlR1C1)
                         sName = "=" & .Name & "!" & sName
                 End With
                 Set sr = cht.SeriesCollection.NewSeries
                 sr.Name = sName
                 sr.Values = rngData
    End Sub

    Try the code as-is in a new workbook. It should create am embedded chart on Sheet1 with data in 5 alternate columns in Sheet2, with sample data and names included. I imagine you will need to change the sheet names to German. Not sure if you also need to translate the INT and RAND formulas.

    Peter Thornton

    Thursday, October 11, 2012 4:51 PM
  • The Problem was, that i had to change the x- and y-axis.

    Here is a the file, with some names changed and only a few users:

    Friday, October 12, 2012 8:31 AM
  • Could you prepare a file that simply gets sample data from sheets. Your code
    attempts to get data from non existent folders in my Outlook.
    Also manually create a chart from the sample data to show what you want the
    programmatically created chart to look like.
    Peter Thornton
    Friday, October 12, 2012 3:52 PM


    I can`t choose all rows because of excel...

    Wednesday, October 31, 2012 3:05 PM
  • It looks like you are trying to make series data with very large numbers of discontiguous / multiarea ranges.

    It would be much better and simpler to make a new "contiguous" set of data based on the original data, according to whatever rules you decide. However you can use defined Names in Series and that overcomes the 255 limit for individual parts of the series formula.

    Working with your testdata2.xls, the following makes 6 series, with the criteria that data must exist in column-B (I'm sure you have some different criteria). Series 1-3 have very small values but series 4-6 have very large values, so I put these on the 2nd axis. When done there are a few values in series-2 which are much larger than anh in series 1&2, so just for demo purposes the code deletes series-2

    Try the following with your testdata2.xls.

    Option Explicit
    Sub makeNames()
    Dim i As Long, j As Long
    Dim cntColumns As Long
    Dim lastRow As Long
    Dim aRng() As Range
    Dim nm As Name
    Dim ws As Worksheet
    Dim wb As Workbook
            cntColumns = 6 + 1
         ReDim aRng(1 To cntColumns)
         Set ws = Worksheets("asdsaa")
         Set wb = ActiveWorkbook
         lastRow = Range("a50000").End(xlUp).Row
            For i = 2 To lastRow
                 If Len(Cells(i, 2)) Then
                         If aRng(1) Is Nothing Then
                                 For j = 1 To cntColumns
                                         Set aRng(j) = Cells(i, j)
                                 For j = 1 To cntColumns
                                         Set aRng(j) = Union(aRng(j), Cells(i, j))
                         End If
                 End If
            Set nm = wb.Names.Add("dataX", aRng(1))
         For i = 2 To cntColumns
                 Set nm = wb.Names.Add("dataY" & i - 1, aRng(i))
    End Sub
    Sub makeChart()
    Dim i As Long
    Dim cntSeries As Long, cntColumns    As Long
    Dim sPrefix As String
    Dim cht As Chart
    Dim sr As Series
    Dim ws As Worksheet
    Dim wb As Workbook
         Set ws = Worksheets("asdsaa")
         Set wb = ActiveWorkbook
         cntSeries = 6
         sPrefix = "='" & wb.Name & "'!"
         Set cht = wb.Charts.Add
         cht.ChartType = xlLine
         cht.SetSourceData ws.Range("a1").Resize(10, cntSeries + 1), _
            For i = 1 To cntSeries
                 Set sr = cht.SeriesCollection(i)
                 If i = 1 Then sr.XValues = sPrefix & "DataX"
                 sr.Values = sPrefix & "DataY" & i
         ' put series 4-6 on the secondary axis
         For i = 4 To 6
                 Set sr = cht.SeriesCollection(i)
                 sr.AxisGroup = xlSecondary
         ' sr-2 data doesn't fit well on the chart
    End Sub
    Sub GetChartData()
    Dim i As Long, j As Long
    Dim cht As Chart
    Dim sr As Series
    Dim ws As Worksheet
    Dim arr2
         Set cht = ActiveChart
         ReDim arr(1 To cht.SeriesCollection.Count + 1)
         Set ws = ActiveWorkbook.Worksheets.Add
         ws.Range("A2") = Now
         Set sr = cht.SeriesCollection(1)
         arr(1) = sr.XValues
         i = 1
         For Each sr In cht.SeriesCollection
                 i = i + 1
                 ws.Cells(1, i) = sr.Name
                 arr(i) = sr.Values
            ReDim arr2(1 To UBound(arr(1)), 1 To UBound(arr))
            For i = 1 To UBound(arr2)
                 For j = 1 To UBound(arr)
                         arr2(i, j) = arr(j)(i)
         ws.Range("A2") = Now
         ws.Range("A2").Resize(UBound(arr2), 1).NumberFormat _
                         = ws.Range("A2").NumberFormat
         ws.Range("A2").Resize(UBound(arr2), UBound(arr)).Value = arr2
    End Sub

    Run makeNames() and makeChart()

    With the new chart active run GetChartData

    Peter Thornton

    Wednesday, October 31, 2012 8:12 PM