Dynamic chart problem

[Ludo]

Hi,

I have a data table where i need to create a chart from.
The data for the chart is in column A (week numbers) and the actual
data is, depending on some situations on column G, or in column N, ...

every chart need the Week column.

I wrote something like (snipset):

Dim ChartWeekSource As Range
Dim ChartDataSource As Range

'Add chart(s)
While Trim(UnitFamilyList(Dummy)) <> ""
Windows(datafilename).Activate
Sheets(UnitFamilyName2).Select
Cells(3, MyOffset).Select
ChartTitle = Selection.Offset(-1, -5).Value
Selection.Offset(0, -2).Select
LastRow = Range(Selection, Selection.End(xlDown)).Rows.Count +
2
If LastRow = 65536 Then LastRow = 4
Range(Selection, Cells(LastRow, ActiveCell.Column)).Select
Set ChartWeekSource = Selection
Selection.Offset(0, 2).Select
Range(Selection, Cells(LastRow, ActiveCell.Column)).Select
Set ChartDataSource = Selection
'
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData
Source:=Sheets(ChartSheet).Range(ChartWeekSource, ChartDataSource),
PlotBy:=xlColumns '<<<<<< problem
ActiveChart.Location Where:=xlLocationAsObject, Name:=ChartSheet
ChartNumber = ActiveSheet.Shapes.Count

I get always a run time error 1004 when i set the data source for the
chart (see <<<<<<)

What am i doing wrong?
Regards,
Ludo

 

[Don Guillett Excel MVP]

Hi,

I have a data table where i need to create a chart from.
The data for the chart is in column A (week numbers) and the actual
data is, depending on some situations on column G, or in column N, ...

every chart need the Week column.

I wrote something like (snipset):

    Dim ChartWeekSource As Range
    Dim ChartDataSource As Range

'Add chart(s)
    While Trim(UnitFamilyList(Dummy)) <> ""
        Windows(datafilename).Activate
        Sheets(UnitFamilyName2).Select
        Cells(3, MyOffset).Select
        ChartTitle = Selection.Offset(-1, -5).Value
        Selection.Offset(0, -2).Select
        LastRow = Range(Selection, Selection.End(xlDown)).Rows.Count +
2
        If LastRow = 65536 Then LastRow = 4
        Range(Selection, Cells(LastRow, ActiveCell.Column)).Select
        Set ChartWeekSource = Selection
        Selection.Offset(0, 2).Select
        Range(Selection, Cells(LastRow, ActiveCell.Column)).Select
        Set ChartDataSource = Selection
'
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData
Source:=Sheets(ChartSheet).Range(ChartWeekSource, ChartDataSource),
PlotBy:=xlColumns      '<<<<<< problem
    ActiveChart.Location Where:=xlLocationAsObject, Name:=ChartSheet
    ChartNumber = ActiveSheet.Shapes.Count

I get always a run time error 1004 when i set the data source for the
chart (see <<<<<<)

What am i doing wrong?
Regards,
Ludo

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."