PDA

View Full Version : Insert Scatter Plot with Variable Range Data?



Doug_L
12-29-2007, 02:15 PM
Hi,
Looking for help with code for inserting a scatterplot with variable range data. As in, the data to be plotted may have a varying number of columns and rows, and I'm having troubles writing code to compensate for this. Any help would be appreciated.

Bob Phillips
12-29-2007, 02:53 PM
Can you post a sample workbook?

Doug_L
12-29-2007, 03:41 PM
I'm unsure what you mean by sample workbook but

With ActiveSheet.ChartObjects.Add _
(Left:=200, Width:=500, Top:=200, Height:=300)
.Chart.SetSourceData Source:=Sheets("Sheet2").Range("A3:G14")
.Chart.ChartType = xlXYScatterLines
End With

I'd want the above to accept a range defined by me, rather than something in A1 notation.
This range would be on sheet 2 and would be anywhere from 12-20 columns and up to 20k rows.

rbrhodes
12-29-2007, 05:37 PM
Hi Doug,

This may give you some ideas? It uses the selection on sheet(x) to build the chart on Sheet1.


Sub chaart()

Dim rng As Range
'Uses selection. could use inputbox to define desired range, or...
Set rng = Selection

'puts chart on Sheet1
With Sheets("Sheet1").ChartObjects.Add _
(Left:=200, Width:=500, Top:=200, Height:=300)
'uses rng variable here
.Chart.SetSourceData Source:=rng
.Chart.ChartType = xlXYScatterLines
End With

'cleanup
set rng = nothing

End Sub

Doug_L
12-31-2007, 12:51 AM
Thanks, the above is good but what I'm looking for would be a way to create a range, based on the output I get.

So, the output I get is 20 columns x 20000 rows (it will always be rectangle, and start at cell: 1, 1) and I'd want it to automatically assign that entire group to a range object, that I could use as the source for the chart.

Doug_L
01-02-2008, 05:33 PM
Ok, so far:

Dim Rng As Range
Set Rng = Worksheets("Sheet2").Range(Cells(1, 1), Cells(J, I))

With Sheets("Sheet1").ChartObjects.Add _
(Left:=200, Width:=500, Top:=200, Height:=300)
.Chart.SetSourceData Source:=Rng
.Chart.ChartType = xlXYScatterLines
End With

That should work, but it gives me an error 1004, application or object-defined error.

However, if I replace the second line to referencing the first page.
Set Rng = Worksheets("Sheet1").Range(Cells(1, 1), Cells(J, I))

I get no problem, and it works fine. How do I get this to refer to the second page?

Dave
01-02-2008, 07:02 PM
HTH. Dave

'make chart
Dim ChartRange As Range, Xvalue As Range, Yvalue As Range
Dim LastRow As Integer
LastRow = Sheets("Sheet1").UsedRange.End(xlDown).Row
Set Xvalue = Sheet1.Cells(1, 1)
Set Yvalue = Sheet1.Cells(LastRow, 2)
Set ChartRange = Sheets("Sheet2").Range(Xvalue, Yvalue)
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=ChartRange, PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

rbrhodes
01-02-2008, 08:45 PM
Hi Doug,

Typically, using the range reference to another sheet requires a little more definition ( I don't know why) but use this:



dim rng as range

'use 'address' with cells definition for sheet 2
Set Rng = Worksheets("Sheet2").Range(Cells(1, 1).Address, Cells(j, I).address)

'your code here


Worked for me...

Doug_L
01-02-2008, 09:17 PM
Thanks very much, it works as I hoped now.