xy scatter source from dynamic range

[sammy.peters]

here is some code from a macro i am making. i would like it to make
an xy chart with my xvalues and yvalues range. these ranges are not
side by side.
as well this is usually creating seperate chart sheets not placing it
on the worksheet id like.
and where would i put .top,.left in this to properly place it.

thanks very much




Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData
Source:=series(Assay_Solutionscrollzoomindynrange.xls!
xvalues,Assay_Solutionscrollzoomindynrange.xls!xvalues,1) _
PlotBy:=xlColumns
With ActiveChart.SeriesCollection.NewSeries
.Values = Range(XValues)
.XValues = Range(YValues)
.name = "Sheet1"
End With


With ActiveChart

.HasTitle = True
.ChartTitle.Characters.Text = Application.VLookup(i,
Range("Graph"), 2) & " ppm vs Time "
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Date Time"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
Application.VLookup(i, Range("Graph"), 2) & " ppm"

End With

 

[Jon Peltier]

See this page for some guidelines:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

1. Create the chart as a chart object in the sheet, not as a new chart
sheet. .ChartObjects.Add() includes arguments for .left, .top, width, and
..height of the chart object.

2. Skip the SetSourceData statement; start with no series, then add each
series and define its X and Y values.

- Jon

 

[sammy.peters]

here is my code

Set spaceforgraph = Range(Cells(zoomrow - 17, zoomcol - 1),
Cells(zoomrow - 1, zoomcol + 6))
i know its inefficient now that it size it once then resize and
locate. i will redo that part but i always get error on the
my.chart.charttype=xlxyscatterlines line. i want scatter not scatter
lines and regardless of how i do it with with statement or jsut on its
own like this i always get an error when compiling of object doesn't
support this property or method.
let me know if this is ok for the series collection part too or if i
have to use with/end with statements as well.

thanks very much
sammy

Set myChart = ActiveSheet.ChartObjects.Add(Left:=Cells(zoomrow
- 15, zoomcol), Width:=300, Top:=Cells(zoomrow - 15, zoomcol),
Height:=175)
With myChart
.Left = spaceforgraph.Left
.Width = spaceforgraph.Width
.Top = spaceforgraph.Top
.Height = spaceforgraph.Height
End With

myChart.ChartType = xlXYScatterLines

myChart.SeriesCollection.NewSeries
myChart.Values = YValues
myChart.XValues = yvlaues
myChart.name = Application.VLookup(i, Range("Graph"), 2, False)

 

[sammy.peters]

I added .chart to the lines
myChart.Chart.ChartType = xlXYScatterLines

myChart.Chart.SeriesCollection.NewSeries
myChart.Chart.Values = Range(YValues)
myChart.Chart.XValues = Range(XValues)
myChart.Chart.name = Application.VLookup(i, Range("Graph"), 2,
False)

so now i get to the values line and it gives me error there. error is
range of method global failed.

this is how my YValues is defined.
yvalues is a string
YValues = Cells(49 + i, 3) & "Yvalue"
that gives it the name of the dynamic range
when i=0 yvalues = "Feed_Yvalue"
and the Feed_Yvalue range is defined as = OFFSET(Feed!$AP$5,scrollval,
0,zoomval,1)

im creating a chart that where there is a pulldown menu on the left
where they select what they are graphing from the worksheet. the
celllinked from the combobox sets the offset in the worksheet where
the data is stored so that the last column =the column selected in the
box. the dynamic range just links to that last column.
this graph is to have both zooming and scrolling abilities. I change
the max for the scroll and zoom bar by linking the index of how many
rows of data there are to a variable and set that variable as the max.
so far everything works i have the scroll bars and zooom which their
maximum changes based on the index and the combobox works and sets the
right offset. i am just having trouble creating the graph. hope you
can help.


if theres anything else i can do to help you understand let me know.

thanks very much.
sammy

 

[Jon Peltier]

What are the keywords XValues and YValues? Are they range variables?
Strings? Should they be in quotes? Wherever you have range, you should
precede it with a reference to the worksheet. Something like this:

Dim sht As Worksheet
Dim myChart As ChartObject

Set sht = ActiveSheet
Set myChart = sht.ChartObjects.Add(... etc...)

' if YValues is a string containing a range
myChart.Chart.Values = sht.Range(YValues)

' if YValues is the name of a range defined on the sheet
myChart.Chart.Values = sht.Range("YValues")

' if YValues is a range
myChart.Chart.Values = YValues


- Jon

 

[sammy.peters]

why do i have to specify the sheet the range is on. doesn't the name
include whichc sheet its on in its definition.


i was thinking that since i am only including the datta points not the
columns heading in the dynrange and most them are 0 for now since no
data is in the table that that could be causing the problem. however i
entered dummy data into the first table and its creating the first
graph and stops at the Value=range(Yvalues) line.

YValues is a string which contains the name of the dynrange to be
graphed on that chart.

if it would help jon I would be glad to send you the workbook if
necessary if it would make it easier to understand.

thanks very much
sammy

 

[sammy.peters]

Thats the definition of the dynrange


ActiveWorkbook.Names.Add name:=namerangex, _
RefersTo:="=OFFSET('" & nameworksheet & "'!$ao$5,scrollval,
0,zoomval,1)"


ActiveWorkbook.Names.Add name:=namerangey, _
RefersTo:="=OFFSET('" & nameworksheet & "'!$ap$5,scrollval,
0,zoomval,1)"


thanks sammy

 

[Jon Peltier]

why do i have to specify the sheet the range is on. doesn't the name

include whichc sheet its on in its definition.

Specifying the sheet name makes the code more robust.

Thats the definition of the dynrange


ActiveWorkbook.Names.Add name:=namerangex, _
RefersTo:="=OFFSET('" & nameworksheet & "'!$ao$5,scrollval,
0,zoomval,1)"


ActiveWorkbook.Names.Add name:=namerangey, _
RefersTo:="=OFFSET('" & nameworksheet & "'!$ap$5,scrollval,
0,zoomval,1)"


thanks sammy

You could use

myChart.Chart.Values = sht.Range(namerangey)

(namerangey is a string, right?) or

myChart.Chart.Values = wbk.Names(namerangey).RefersToRange

where wbk is a workbook variable set to the workbook that contains sht,
i.e., the active workbook when the code started running.

- Jon