Creating a chart using a cell as the range reference

  • Thread starter Thread starter jtklepp
  • Start date Start date
J

jtklepp

Hi.

I have set up a macro where you can type in the name and title of th
chart in a cell, and the range, run the macro and it creates th
chart.

Trouble is that taking the name from a cell is easy:
.ChartTitle.Characters.Text = Sheets("Sheetname").Range("Y26")

But I cannot do this for a range:
ActiveChart.SetSourceData Source:=Sheets("Tables").Range("Y25")
PlotBy:= _
xlRows

The Range in this formula should point to a variable (for instanc
A1:A10) and I want to be able to type that range into the cell (in thi
case Y25).

Is there a way to define a variable that gets the value from the cel
(Y25), and use the variable in the Range object?

Regards,
J
 
You're telling it to use Y26 as the source data, rather than the value in Y26. Try
something like this:

ActiveChart.SetSourceData _
Source:=Sheets("Tables").Range(Sheets("Tables").Range("Y25").Value)

Myself, I find it a PITA to enter a cell reference into a cell. You might consider
having the macro ask the user for the range with an input box:

Dim MyRange as Range
Set MyRange = Application.InputBox("Select a range",,,,,,,8)
ActiveChart.SetSourceData Source:=MyRange, PlotBy:=xlRows

You could use InputBox for chart name and title, as well.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Great, Thank you! That seemed to work just fine.

I do get an error though, when I try to use the inputbox to type in th
name:
Dim MyName As Name
Set MyName = Application.InputBox("Type a name", , , , , , , 2)
ActiveChart.SeriesCollection(1).Name = MyName

Is this not correct?

J
 
Back
Top