Insert Scatter Plot with Variable Range Data?

[Doug_L]

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]

Can you post a sample workbook?

[Doug_L]

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]

Hi Doug,

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

[vba]
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

[/vba]

[Doug_L]

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]

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]

HTH. Dave
[VBA]
'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"
[/VBA]

[rbrhodes]

Hi Doug,

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

[vba]

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
[/vba]

Worked for me...

[Doug_L]

Thanks very much, it works as I hoped now.