# Using Cells() for creating charts? (german Excel 2003) • ### 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))), _
```

Thanks

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
Wend
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), _
PlotBy:=xlColumns
'ActiveChart.SetSourceData Source:=Sheets("temp").Range("1:1,3:3,5:5,7:7"), _
PlotBy:=xlColumns
```

The debug print of Diagrammzeilen is:

```"1:1,3:3,5:5,7:7,9:9,11:11,13:13,15:15,17:17,19:19,21:21,23:23,25:25,27:27,29:29,31:31,33:33,35:35,37:37,39:39,41:41,43:43,45:45,47:47,49:49,51:51,53:53,55:55,57:57,59:59,61:61,63:63,65:65,67:67,69:69,71:71,73:73,75:75,77:77,79:79,81:81,83:83,85:85,87:87,89:89,91:91,93:93,95:95,97:97,99:99,101:101,103:103,105:105,107:107,109:109,111:111,113:113,115:115,117:117,119:119,121:121,123:123,125:125,127:127,129:129,131:131,133:133,135:135,137:137,139:139,141:141,143:143,145:145,147:147,149:149,151:151,153:153,155:155,157:157,159:159,161:161,163:163,165:165,167:167,169:169,171:171,173:173,175:175,177:177,179:179,181:181,183:183,185:185,187:187,189:189,191:191,193:193,195:195,197:197,199:199,201:201,203:203,205:205,207:207,209:209,211:211,213:213,215:215,217:217,219:219,221:221,223:223,225:225,227:227,229:229,231:231,233:233,235:235,237:237,239:239,241:241,243:243,245:245,247:247,249:249,251:251,253:253,255:255,257:257,259:259,261:261,263:263,265:265,267:267,269:269,271:271,273:273,275:275,277:277,279:279,281:281,283:
283,285:285,287:287,289:289,291:291,293:293,295:295,297:297,299:299,301:301,303:303,305:305,307:307"```

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
.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)"
xlA1, xlR1C1)
sName = "=" & .Name & "!" & sName
End With
Set sr = cht.SeriesCollection.NewSeries
sr.Name = sName
sr.Values = rngData
Next
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:

http://jokergermany.bplaced.net/testdata.xls

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
• http://jokergermany.bplaced.net/testdata.xls

Done

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)
Next
Else
For j = 1 To cntColumns
Set aRng(j) = Union(aRng(j), Cells(i, j))
Next
End If
End If
Next
For i = 2 To cntColumns
Set nm = wb.Names.Add("dataY" & i - 1, aRng(i))
Next

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 & "'!"
cht.ChartType = xlLine
cht.SetSourceData ws.Range("a1").Resize(10, cntSeries + 1), _
PlotBy:=xlColumns
For i = 1 To cntSeries
Set sr = cht.SeriesCollection(i)
If i = 1 Then sr.XValues = sPrefix & "DataX"
sr.Values = sPrefix & "DataY" & i
Next
' put series 4-6 on the secondary axis
For i = 4 To 6
Set sr = cht.SeriesCollection(i)
sr.AxisGroup = xlSecondary
Next
' sr-2 data doesn't fit well on the chart
cht.SeriesCollection(2).Delete
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)
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
Next
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)
Next
Next
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