Charting multiple sheets with arrays

[pugap]

Working on a spreadsheet that imports data from text files, based on user input. For each set of data, a new sheet is created and the data is put on that sheet. An input box allows the user to name the sheet as the data is being input. All this works ok. I've also got a routine that puts the sheet names into an array (i.e. SheetName(15)). All of the data has the same format and same number of rows.

Now the part that's not working. I want to be able to plot the data on an xyscatter chart. Since I won't know how many sheets are read in ahead of time, I first count the number of sheets (SheetCount). Then I loop through the sheets, and try to get the X & Y data. I assign the ranges to a stringname (XRange, YRange).
The code looks something like this:

SheetCount = ThisWorkbook.Sheets.Count
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
For intLoop = 1 To SheetCount
xRange = "=" & SheetName(intLoop) & "!R2C2:R467C2"
yRange = "=" & SheetName(intLoop) & "!R2C1:R467C1"

ActiveChart.SeriesCollection.NewSeries
******THE NEXT LINE IS WHERE IT HANGS UP*********
ActiveChart.SeriesCollection(intLoop).xValues = xRange
ActiveChart.SeriesCollection(intLoop).Values = yRange

Next intLoop

The error message is "Unable to set the XValues property of the series class". I'm not sure why, because it is looking at the right sheet name when it runs through the loop. Any ideas on what to look for? Thanks in advance.

 

[johnwm]

I don't think you can construct a Range object just from a string. You can however loop through the Sheets using their Index property. If you add a Chart (as above) then your Sheets (excluding the Chart which will be at Sheets(1)) will be numbered from 2 to Sheetcount + 1. Try something like:

Private Sub cmdChart_Click()
sheetcount = ThisWorkbook.Sheets.Count
    Charts.Add
    For a = 2 To sheetcount + 1
      ActiveChart.SeriesCollection.NewSeries
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
    ActiveChart.SetSourceData Source:=Sheets(a).Range("A2:B467"), PlotBy:=xlColumns
    MsgBox "next"
Next a
End Sub

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk

 

[m777182]

Once I had a similar problem. I cannot find the original code but the problem is that you should first declare your variables xRange and yRange as Range. Then you have to assign to them a range like this:
with worksheets("your worksheet name")
set xRange=.Range(.cells(start of xRange),.cells(end of xRange)))
set YRange=.range(.cells(start of yRange),.cells(end of yRange))
end with

Your instructions
xRange = "=" & SheetName(intLoop) & "!R2C2:R467C2"
yRange = "=" & SheetName(intLoop) & "!R2C1:R467C1"
didn't do that.
I hope it will work.
m777182

 

[pugap]

m777182
Looks promising, but I think I may still be missing something. I get the same error message at the same line. I put your four lines in place of where I originally had xRange & yRange, with "your worksheet name" as SheetName(intloop)

 

[m777182]

pugap, what error message do you get? I shall try to find my code and go through it over the weekend time.
m777182

 

[pugap]

Same error message on the same line as before: "Unable to set the XValues property of the series class". I'm thinking it's got something to do with referencing the worksheet with an array. I've been finding examples similar to what you noted, but most use embedded charts on the same sheet as the data. I wouldn't think it would be that different, but I'm still missing something.

 

[johnwm]

Did you try my previous suggestion - which I know works (having tested it)

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk

 

[pugap]

johnwm,

Sorry to not respond, but thanks for the input.

It will work, but I should have expanded a little more on what I need. One problem is that my X data is in the second column, and my Y data is in the first. Also, knowing how one feature inspires requests for more features, I would like to keep it more flexible to be able to choose different columns with minimal coding changes. I guess with your function I can't tell what columns would be used if I were to expand the range. Also need it to where the chart is a new sheet, and not embedded in the same sheet as the data.

 

[johnwm]

Simple, sorry I didn't understand your original question:

Dim b As Series
Dim xrange As Range
Dim yrange As Range

sheetcount = ThisWorkbook.Sheets.Count
    Charts.Add
    For a = 2 To sheetcount + 1
     Set b = ActiveChart.SeriesCollection.NewSeries
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
    Set xrange = Sheets(a).Range("a12:a167")
    Set yrange = Sheets(a).Range("b12:b167")
    b.XValues = xrange
     b.Values = yrange
Next a

Need to remember to use the Set keyword when assigning values to Objects. The SeriesCollection can only return values, you need to set the individual Series properties when you generate each series (as shown). BTW both this code and my original post start a new Chart sheet

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk

 

[m777182]

I think you have saved your problem with johnwms's piece of code. Mine is very similar. You can help yourself by recording a macro and then you look into it what code does it generate. Then you transfer it to your VBa -with some modification sometimes.
good luck
m777182

 

[pmover]

something to ponder...

why not use dynamic arrays? granted the dynamic array will need be created for each sheet, but at least the vba code (if decision to write vba) can be simplified.

perhaps a workbook can be prepared such that all the user does is import the data (may have to use the copy & paste-values when import takes place). the dynamic arrays will automatically size themselves to the range of data.

it is a practice of mine to maximize the std features of xl without writing code, unless the code simplify's the process.

please note the following website regarding dynamic arrays and charting:

http://www.ozgrid.com/Excel/DynamicRanges.htm

good luck!
-pmover

 

[pugap]

Thanks all. Came up with basically what was suggested, including a way to work with a dynamic range. I think the problem had to do with how some of my array counters were being passed between subs. Basically kept this routine, and changed some others, and eventually got it to work.