Surface Plot Creation

[Ben H]

Hey Guys.

Got a really goofy question here. I'm trying to create a macro that will
take data from the ranges of C3 to CY103 and create a surface plot with it.
This is what I have recorded and its spitting an error out no matter what I
try to do.

************************************
Sheets("Surf Plot Data").Select
Charts.Add
ActiveChart.ChartType = xlSurface
ActiveChart.SetSourceData Source:=Sheets("Surf Plot
Data").Range("C3:CY103") _
, PlotBy:=xlRows
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:= _
"i hate surface plots"
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlRight
Charts.Add
************************************

Error Message is:
"A surface chart must contain at least two series"

No matter where I try to move the xlSurface command, i still get error
messages and two plots created...

Thanks for the Help
Ben H

 

[Andy Pope]

Hi,

Try this subtle change.

Sheets("Surf Plot Data").Select
Charts.Add
ActiveChart.SetSourceData Source:= _
Sheets("Surf Plot Data").Range("C3:CY103") _
, PlotBy:=xlRows
ActiveChart.ChartType = xlSurface

Cheers
Andy

 

[Ben H]

Andy

Exactly what I was looking for. Thanks!

Now for a different question. I'm trying to put a dynamic range in the
..Range() command but it is spitting an type mis-match error out. I've got
this so far
"
rng = Sheets(strSurfName).Range(Cells(3, 3), Cells(3,
3).End(xlDown).End(xlToRight))
"
and
"
ActiveChart.SetSourceData Source:= _
Sheets(strSurfName).Range(rng) _
, PlotBy:=xlRows
"

Thanks - Ben H.

 

[Andy Pope]

This works for me,

Dim rng As Range
Dim strSurfName As String

strSurfName = "Sheet1"
With Sheets(strSurfName)
Set rng = .Range(.Cells(3, 3), _
.Cells(3, 3).End(xlDown).End(xlToRight))
End With

ActiveChart.SetSourceData Source:=rng _
, PlotBy:=xlRows

Cheers
Andy

 

[Ben H]

Andy

Exactly what I needed. Amazing

Thank you Greatly!
Ben H.