plotting a named range within a named range

[altbeb]

Hi:
My first post .

So l have a set of data that was plotted on a previous graph, and using a userform l have selected the named ranges that l need to look at more closely, and have dump them on the next sheet in a named range called "ChanelSelRnge". The code below is my attempt to replot these data sets from the named range, but although l get the plot to graph, it only plots a single point that correspond to the named ranges. Confusing?.. to me too . I get the runtimne error 1004. that stipulates that the range method fails.. any help would be appreciated.

Code:\
sFileName = ActiveWorkbook.Name

Application.Goto Sheets("CorrectedData").Range("ChanelSelRnge")

ChannelsNum = Application.WorksheetFunction.CountIf(Sheets("CorrectedData").Range("Chanel SelRnge"), "*")

MsgBox "The result is " & ChannelsNum

NameCht1 = Left(sFileName, Len(sFileName) - 4) & "a"

Set ChtChart1 = Charts.Add
ChtChart1.Move after:=Worksheets("Data")
With ChtChart1
.Name = NameCht1
.HasTitle = True
.ChartTitle.Text = NameCht1
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time(secs)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Wall Displacement(mm)& Wall Acceleration (m/s2)"
.ChartType = xlXYScatter
'Do Until .SeriesCollection.Count = 0
'.SeriesCollection(1).Delete
'Loop


For i = 1 To ChannelsNum

With ChtChart1.SeriesCollection.NewSeries
.Values = Worksheets("CorrectedData").Range("ChanelSelRnge")(i)
.Name = Range("ChanelSelRnge")(i)
.XValues = Worksheets("Data").Range("Time_Duration1")

End With
Next
End With

EndCode:\

Thxks

Ben

[p45cal]

What is the shape of Range("ChanelSelRnge") (rows x columns)? Does it have headers? Are those headers at the top or on the left?

Why do I ask? CountIf(Sheets("CorrectedData").Range("Chanel SelRnge"), "*") returns 0 here if there are just numerical values in the range, but does count the number of cells with text in.

I'm not sure what you're trying to do but I suspect that instead of adding multiple series you should try to add multiple points to a single series. This can be done with one hit with the likes of:
[vba].Values = Worksheets("CorrectedData").Range("ChanelSelRnge")[/vba] assuming that range is one column wide or one row wide
[Worksheets("CorrectedData").Range("ChanelSelRnge")(i) returns just one value]
or perhaps:
[vba].Values = Worksheets("CorrectedData").Range("ChanelSelRnge").columns(1)[/vba]
Likewise, what is the shape of the range Time_Duration1?

I'm very much in the dark about what kinds of data are in the ranges and what you're trying to do. A sample workbook might be useful, but you may need to post a minimum of a few posts before you're allowed attach files.

ps. I've assumed that in this line:
[vba]ChannelsNum = Application.WorksheetFunction.CountIf(Sheets("CorrectedData").Range("Chanel SelRnge"), "*")[/vba] there shouldn't be a space in the red name.

pps. What version of Excel?

[altbeb]

Hi p45cal:

The ChanelSelRnge is a 1x16 range, with no headers and is all strings..

this is the range that the userform selections are dumped into, each selection is itself a named range, and l need to plot each individual cell in ChanelSelRnge. I hope that makes sense.
I'm adding the prior code that actual graphs all the data series.. 16 with in each 3000 data points.

CODE:|
Sub ChartAdded()

Dim ChtChart As Chart
Dim NameCht As String
Dim sFileName As String

Dim Data As Name
Dim i As Integer
Dim SmoothedDataSet() As String
Dim Time_Duration1 As String
Dim Channels() As String



sFileName = ActiveWorkbook.Name

NameCht = Left(sFileName, Len(sFileName) - 4)

'Create a new chart.
Set ChtChart = Charts.Add
ChtChart.Move after:=Worksheets("Data")
With ChtChart
.Name = NameCht
.HasTitle = True
.ChartTitle.Text = NameCht
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time(secs)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Wall Displacement(mm)& Wall Acceleration (m/s2)"
.ChartType = xlXYScatter
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
End With



For i = 1 To 16

With ChtChart.SeriesCollection.NewSeries
.Values = Worksheets("Data").Range("SmoothedDataSet" & i & "")
.Name = "Channels" & i & ""
.XValues = Worksheets("Data").Range("Time_Duration1")

End With


Next

End Sub

Public Sub CorrctDataHeadings()
Dim ChannelHeadings As String

Dim ChanelSelRnge As Range

ActiveWorkbook.Worksheets("CorrectedData").Select


Range("A3").Select
ActiveCell.FormulaR1C1 = "Channel Selection & Re-Plotting"
Range("A5:A21").Name = "ChanelSelRnge"
Range("A3").Select
Selection.Font.Bold = True
Worksheets("Data").Select


End Sub

END CODE:\



Question: Is excel able to discern that within a range there are ranges on previous worksheets? I attempt to record a macro of what l am attempting to do , and excel just plotted 3 points corresponding to the three series, instead of the 3000 points contained in each data series.. any help would be great.. i've been doing this fir the last two days.. .. and this is what l am doing now...

[p45cal]

So you have a list of named ranges' names in Range("ChanelSelRnge"), with the likes of:
SmoothedDataSet3
SmoothedDataSet6
SmoothedDataSet12

If so try (untested):
[vba].Values = Worksheets("Data").Range(Worksheets("CorrectedData").Range("ChanelSelRnge") (i))
[/vba] and you might get away with:
[vba].Values = Worksheets("Data").Range(Range("ChanelSelRnge")(i))[/vba]
or even:
[VBA].Values = Range(Range("ChanelSelRnge")(i))[/VBA]

[altbeb]

Hi
yep.. that's right..
the codes all come up with the run time error 1004..l am sry to say.."Method 'Range of object_Global failed" or "Application Defined or object defined error"

might it be excel 2010?.. l am running office 2010...not a bad package.. a first for MS..

thxks for the assist

[p45cal]

if you can attach a file...
PM me if t's sensitive

[altbeb]

Hi:

attached is the excel workbook that l use for each data set, need to run module one for the complete calcs.

thxks

[p45cal]

Thank you for that, things are beginning to look clearer. There is a snag; when I try to run module 1 it asks for a data file which I don't have. So could you either attach such a data file or send an xlsm file with the SmoothedDataSets named ranges in place so that I can try to run the plotting procedure (again, PM me if this is sensitive data). Currently those named ranges have reference errors:
Attachment 3855

We can however make some progress which may solve your problem straightaway:
ChanelSelRnge has text in it like
' SmoothDataSet3'.

1. This has a leading space
2. The named ranges in your workbook are named like 'SmoothedDataSet3'

Both of these will cause errors. Rectify these differences and it might work. You should be able to use my last suggestion:
[vba].Values = Range(Range("ChanelSelRnge")(i)) [/vba] because all named ranges in your workbook have the workbook scope and not a worksheet scope.

[altbeb]

Hey p45cal:

many thxks for the assist.. much appreciated..that was the error, and your solution was spot on..

altbeb