Problem with empty cells when creating chart using vba
 

Hello,

I am facing difficulties to create a chart from a sheet containing empty
cells (I use Excel 2003 SP2). My sheet looks similar to this:

A B C

1 0.1

2 0.2 25 34

3 0.3 23 27

4 0.4 24 6

5 0.5 25 16

6 0.6 27 21

7 0.7


Column A contains data (some depth in my case). Each cell in column A
contains data. Column B and C contain some measurements made at the depth
indicated in column A. I need to combine two graphics on one chart: the
content of B in function of A and the content of C in function of A.

The following code works fine:


With Graph

.ChartType = xlLine
.SetSourceData Source:=Sheets(MainSheet).Range("B2:C6"),
PlotBy:=xlColumns
.SeriesCollection(1).XValues = Sheets(MainSheet).Range("A2:A6")

(...)


The result of this code is a chart containing two lines, for X values rangin
from 0.2 to 0.6. Now, what I really need is to have the X values ranging
from 0.1 to 0.7 and keep the lines displayed for values 0.2 - 0.6. So I
change my code to:


With Graph

.ChartType = xlLine
.SetSourceData Source:=Sheets(MainSheet).Range("B1:C7"),
PlotBy:=xlColumns
.SeriesCollection(1).XValues = Sheets(MainSheet).Range("A1:A7")

(...)


And in this case, only ONE line is displayed (values of column C) ???

I have been working on this for two nights and this is driving me nuts.

Any help would be appreciated.


Thanks,

Xavier

Problem with empty cells when creating chart using vba
 

Hi,

Excel is trying to guess your data layout and empty cells do not help.
Try using the Formula property of a series.

Sub X
Dim Graph As Chart
Dim MainSheet As String
Dim strXValues As String
Dim strValues As String

MainSheet = "Sheet2"
Set Graph = ActiveSheet.ChartObjects(1).Chart

With Graph
.ChartType = xlLine
'.SetSourceData Source:=Sheets(MainSheet).Range("A2:C7"), _
PlotBy:=xlColumns
.SeriesCollection.NewSeries
strXValues = "'" & MainSheet & "'!" & _
Sheets(MainSheet).Range("A2:A7").Address
strValues = "'" & MainSheet & "'!" & _
Sheets(MainSheet).Range("B2:B7").Address
.SeriesCollection(1).Formula = "=SERIES(," & _
strXValues & "," & strValues & ",1)"
.SeriesCollection.NewSeries
strValues = "'" & MainSheet & "'!" & _
Sheets(MainSheet).Range("C2:C7").Address
.SeriesCollection(2).Formula = "=SERIES(," & _
strXValues & "," & strValues & ",2)"
End With
End sub

Cheers
Andy

Xavier wrote:
Hello,

I am facing difficulties to create a chart from a sheet containing empty
cells (I use Excel 2003 SP2). My sheet looks similar to this:

A B C

1 0.1

2 0.2 25 34

3 0.3 23 27

4 0.4 24 6

5 0.5 25 16

6 0.6 27 21

7 0.7


Column A contains data (some depth in my case). Each cell in column A
contains data. Column B and C contain some measurements made at the depth
indicated in column A. I need to combine two graphics on one chart: the
content of B in function of A and the content of C in function of A.

The following code works fine:


With Graph

.ChartType = xlLine
.SetSourceData Source:=Sheets(MainSheet).Range("B2:C6"),
PlotBy:=xlColumns
.SeriesCollection(1).XValues = Sheets(MainSheet).Range("A2:A6")

(...)


The result of this code is a chart containing two lines, for X values rangin
from 0.2 to 0.6. Now, what I really need is to have the X values ranging
from 0.1 to 0.7 and keep the lines displayed for values 0.2 - 0.6. So I
change my code to:


With Graph

.ChartType = xlLine
.SetSourceData Source:=Sheets(MainSheet).Range("B1:C7"),
PlotBy:=xlColumns
.SeriesCollection(1).XValues = Sheets(MainSheet).Range("A1:A7")

(...)


And in this case, only ONE line is displayed (values of column C) ???

I have been working on this for two nights and this is driving me nuts.

Any help would be appreciated.


Thanks,

Xavier



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Problem with empty cells when creating chart using vba
 

Hi Andy,

Thanks a lot for your input, it works beautifully.
Do you have any pointer describing the technique you suggested (and why) ?

Thanks again,

Xavier


"Andy Pope" wrote in message
...
Hi,

Excel is trying to guess your data layout and empty cells do not help.
Try using the Formula property of a series.

Sub X
Dim Graph As Chart
Dim MainSheet As String
Dim strXValues As String
Dim strValues As String

MainSheet = "Sheet2"
Set Graph = ActiveSheet.ChartObjects(1).Chart

With Graph
.ChartType = xlLine
'.SetSourceData Source:=Sheets(MainSheet).Range("A2:C7"), _
PlotBy:=xlColumns
.SeriesCollection.NewSeries
strXValues = "'" & MainSheet & "'!" & _
Sheets(MainSheet).Range("A2:A7").Address
strValues = "'" & MainSheet & "'!" & _
Sheets(MainSheet).Range("B2:B7").Address
.SeriesCollection(1).Formula = "=SERIES(," & _
strXValues & "," & strValues & ",1)"
.SeriesCollection.NewSeries
strValues = "'" & MainSheet & "'!" & _
Sheets(MainSheet).Range("C2:C7").Address
.SeriesCollection(2).Formula = "=SERIES(," & _
strXValues & "," & strValues & ",2)"
End With
End sub

Cheers
Andy

Xavier wrote:
Hello,

I am facing difficulties to create a chart from a sheet containing empty
cells (I use Excel 2003 SP2). My sheet looks similar to this:

A B C

1 0.1

2 0.2 25 34

3 0.3 23 27

4 0.4 24 6

5 0.5 25 16

6 0.6 27 21

7 0.7


Column A contains data (some depth in my case). Each cell in column A
contains data. Column B and C contain some measurements made at the depth
indicated in column A. I need to combine two graphics on one chart: the
content of B in function of A and the content of C in function of A.

The following code works fine:


With Graph

.ChartType = xlLine
.SetSourceData Source:=Sheets(MainSheet).Range("B2:C6"),
PlotBy:=xlColumns
.SeriesCollection(1).XValues = Sheets(MainSheet).Range("A2:A6")

(...)


The result of this code is a chart containing two lines, for X values
rangin from 0.2 to 0.6. Now, what I really need is to have the X values
ranging from 0.1 to 0.7 and keep the lines displayed for values 0.2 -
0.6. So I change my code to:


With Graph

.ChartType = xlLine
.SetSourceData Source:=Sheets(MainSheet).Range("B1:C7"),
PlotBy:=xlColumns
.SeriesCollection(1).XValues = Sheets(MainSheet).Range("A1:A7")

(...)


And in this case, only ONE line is displayed (values of column C) ???

I have been working on this for two nights and this is driving me nuts.

Any help would be appreciated.


Thanks,

Xavier

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info