Looking for help with macros and chart making.

[Guest]

My spreadsheet: Rows A thru BU, Columns 1 thru 34. Keeps tracks of stats.
What I'm trying to do: I want the user to be able to click a button (or a
hotkey) to create a chart that will show a columns data so that they can
visualy see the evolution of a particular stat.

Where I currently stand: I can accomplish this with a macro but only for one
column. Here's my code...

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 1/26/2005 by
'
'
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("December 2004").Range( _
"B4:B34,J4:J34"), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="December 2004"
ActiveChart.HasLegend = False

End Sub

Is there a way to change 'ActiveChart.SetSourceData Source:=Sheets("December
2004").Range("B4:B34,J4:J34"), PlotBy:=xlColumns' so that instead of a static
range it uses where ever cell the user has selected to generate the chart?
What I want exactly is for the user to be able to point at say B4 and then
start a macro which will create a chart like i made above using a range of
B4:B34. But, if the user selects C4 I want a chart to be made with a range of
C4:C34. I am very much a beginner at coding in Excel and not sure how to
accomplish this.

Any help would be much appriciated!
-mike

 

[Tom Ogilvy]

' Macro3 Macro
' Macro recorded 1/26/2005 by
'
'
Dim sStr as String
sStr = cells(4,ActiveCell.Column).Resize(31,1).Address(0,0) & _
",J4:J34"
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("December 2004").Range( _
sStr), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="December 2004"
ActiveChart.HasLegend = False

End Sub

 

[Don Guillett]

One easier way to do this would be to set up a defined name range such as
mycol=offset($a$4,0,column()-1,counta($A:$A),1) and then a double click
event to force calculation.
Then your series is set to =yourworkbookname.xls!mycol
Now when you double click the column it will chart that column to the
longest row in col A.