Charting multiple sheets with arrays
Charting multiple sheets with arrays
(OP)
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.
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.





RE: Charting multiple sheets with arrays
CODE
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: www.essexsteam.co.uk
RE: Charting multiple sheets with arrays
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
RE: Charting multiple sheets with arrays
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)
RE: Charting multiple sheets with arrays
m777182
RE: Charting multiple sheets with arrays
RE: Charting multiple sheets with arrays
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
UK steam enthusiasts: www.essexsteam.co.uk
RE: Charting multiple sheets with arrays
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.
RE: Charting multiple sheets with arrays
CODE
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: www.essexsteam.co.uk
RE: Charting multiple sheets with arrays
good luck
m777182
RE: Charting multiple sheets with arrays
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
RE: Charting multiple sheets with arrays