0

I'm creating a chart in sheet2 from date from sheet3 but I get a "Script out of range" error when I set the .setsourcedata to sheet3. If I don't define it, it runs but plots the range from sheet2. Same error if I add sheet3 when defining the x/y values:

.SeriesCollection(1).XValues = sheet3.Range(Cells(irow, icolumnu), Cells(frowu, icolumnu))

Help me please!

Set cht = Sheet2.Shapes.AddChart.Chart

With cht
    .ChartType = xlXYScatterSmooth
    .Axes(xlValue).ScaleType = xlScaleLogarithmic
    .SeriesCollection.NewSeries
    .SetSourceData Source:=Worksheets("sheet3").Range(Cells(irow, icolumnu), Cells(frowu, icolumnu + 1))
    .SeriesCollection(1).Name = Sheet3.Cells(2, icolumnu).Value 'upper curve
        '.Name = Sheet3.Cells(2, icolumnu).Value
    .SeriesCollection(1).XValues = Range(Cells(irow, icolumnu), Cells(frowu, icolumnu))
    .SeriesCollection(1).Values = Range(Cells(irow, icolumnu + 1), Cells(frowu, icolumnu + 1))
    'End With
    .SeriesCollection.NewSeries
    .SetSourceData Source:=Worksheets("sheet3").Range(Cells(irow, icolumnl), Cells(frowl, icolumnl + 1))
    .SeriesCollection(2).Name = Sheet3.Cells(2, icolumnl).Value
    .SeriesCollection(2).XValues = Range(Cells(irow, icolumnl), Cells(frowl, icolumnl))
    .SeriesCollection(2).Values = Range(Cells(irow, icolumnl + 1), Cells(frowl, icolumnl + 1))
       ' End With
End With
braX
  • 11,506
  • 5
  • 20
  • 33
hyperquad
  • 3
  • 2
  • [`Range(Cells, Cells)` needs to have the `Cells` qualified](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells). – BigBen Sep 22 '20 at 16:07
  • `Cells` can refer to any worksheet, and without qualification, it assumes the active one, which is not what you want in this case. You need to explicitly tell it to use the same worksheet as the range is using. – braX Sep 22 '20 at 16:34
  • Dooaah Thanks BigBen! – hyperquad Sep 22 '20 at 17:17

0 Answers0