×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Charting multiple sheets with arrays

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.

RE: Charting multiple sheets with arrays

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:

CODE

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: www.essexsteam.co.uk

RE: Charting multiple sheets with arrays

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

RE: Charting multiple sheets with arrays

(OP)
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)

RE: Charting multiple sheets with arrays

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

RE: Charting multiple sheets with arrays

(OP)
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.

RE: Charting multiple sheets with arrays

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: www.essexsteam.co.uk

RE: Charting multiple sheets with arrays

(OP)
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.

RE: Charting multiple sheets with arrays

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

CODE

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: www.essexsteam.co.uk

RE: Charting multiple sheets with arrays

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

RE: Charting multiple sheets with arrays

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

RE: Charting multiple sheets with arrays

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close