Solved: columnstacked chart problem

[jrm]

Hi everyone,

Simple problem (=> simple solution, i hope ?),

i need to do a column stacked chart with two columns of data, and for some reasons, despite my code below, excel displays 4 sets of data :o

any idea why ?

[VBA]Sub graphempile()
Dim visuel As Chart, k As Long
Cells(4, 2).Select
' selects the first cell of my first data series
k = (Range("B4").End(xlDown).Row)
goes until last row which is not empty
Set visuel = Charts.Add
visuel.ChartType = xlColumnStacked
visuel.SetSourceData Source:=Range("'data'!$B$4:$B" & k & ";'data'!$D$4:$D" & k & "")
End Sub[/VBA]

Thanks a lot for your help !

[p45cal]

Could you give us a clue as to what kind of data are in B4:Dnn? Any headers there? Data Type etc. You are relying on Excel automatically choosing how to plot data for you. Perhaps attach a small workbook with just a simple illustration of what you have? Even a version of the chart prepared maually as you would like vba to duplicate?

[jrm]

kind of data that are in B4nn : plain numbers.
header on B1 : text

what do you mean i'm relying on Excel automatically choosing how to plot data for me ? isn't visuel.SetSourceData Source:=Range("'data'!$B$4:$B" & k & ";'data'!$D$4:$D" & k & "") sufficient ?

please see attachment

[p45cal]

Well, your code worked for me except I had to change a semicolon to a comma here:
visuel.SetSourceData Source:=Range("'data'!$B$4:$B" & k & ";'data'!$D$4:$D" & k & "")
to:
visuel.SetSourceData Source:=Range("'data'!$B$4:$B" & k & ",'data'!$D$4:$D" & k & "")
but that may be because my locale is UK, but try that first.

Next, if you step through your code (using the F8 key) observing what happens, it initially creates a 4-series plot, which then gets changed to 2-series plot. It does this because when the chart is first added, it tries to work out what to plot from what is currently selected. In this case, you had selected the single cell B4 (=Cells(4,2), so it tries to look at what's around that cell, and plots what it finds - 4 series in this case.

You don't need to select any cells theoretically. Remove that line.

If the code still fails, you could try selecting just what you want to plot before adding the chart:
[vba]Sub graphempile()
Dim visuel As Chart, k As Long
k = (Range("B4").End(xlDown).Row)
Range("$B$4:$B" & k & ",$D$4:$D" & k).Select
Set visuel = Charts.Add
visuel.ChartType = xlColumnStacked
End Sub
[/vba]That worked here.

Or, make sure there's only a single cell selected with nothing around it before adding the chart:
[vba]Sub graphempile()
Dim visuel As Chart, k As Long
k = (Range("B4").End(xlDown).Row)
Range("G2").Select
Set visuel = Charts.Add
visuel.ChartType = xlColumnStacked
visuel.SetSourceData Source:=Sheets("data").Range("$B$4:$B" & k & ",$D$4:$D" & k)
End Sub
[/vba]That worked too (step through the code with F8 to watch it creating a blank chart in the first instance).

None of this should be needed at all though; the sourcedata should be corrected properly at the end. I've simplified that line a bit. Hopefully you won't need to change the comma back to a semicolon.

So my preferred solution is:[VBA]Sub graphempile()
Dim visuel As Chart, k As Long
k = (Range("B4").End(xlDown).Row)
Set visuel = Charts.Add
visuel.ChartType = xlColumnStacked
visuel.SetSourceData Source:=Sheets("data").Range("$B$4:$B" & k & ",$D$4:$D" & k)
End Sub
[/VBA]
If you still can't get what you want, there is a way of adding a blank chart and adding series individually. Post again if we need to go down that route.

[jrm]

Thanks a lot for all your explanations. This "plotting" thing is now clear to me.
Changing the semicolon to a comma worked well, even though my Excel is in French.

Have a good weekend.

Jrm