Graphing variable data

[jamesgw]

I am creating a bar stacked bar chart. I send over data from access and open up excel thru automation. I have created a macro that creates this chart ... however,

the macro HARDCODES the initial data range. When I send over new data (with more rows), the macro, of course, only selects the previous datarange I sent.

Can anyone help me with code that I could use that would allow for a variable data range to be input into the "SETSOURCEDATA" line of my macro?

Thanks!!
(e.g. data)

Indication Offset Duration
Ind_1 2 5
Ind_2 4 5
Ind_3 6 9
(Above cells DATARANGE a1:c3

Indication Offset Duration
Ind_1 2 5
Ind_2 4 5
Ind_3 6 9
Ind_4 7 2
Ind_5 8 7
Ind_6 9 4
(When I send THIS data over, my macro still only uses the previous A1:C3 range!!!!


THANKS!!!

[ This Message was edited by: jamesgw on 2002-05-10 11:20 ]

[ This Message was edited by: jamesgw on 2002-05-10 11:22 ]

[zzydhf]

here is one I use - I have fomulas at j1 and k1 that find my range - you will need to change it to suit your needs of course

Sub set_chart_range()
'
' Macro3 Macro
' Macro recorded 01/11/2001 by Keith O'Brien
'

Dim chart_range1 As Integer
Dim chart_range2 As Integer
Dim range_name1 As String
Dim range_name2 As String
chart_range1 = Cells(1, 10).Value
chart_range2 = Cells(1, 11).Value
range_name1 = "A2,L2:M2,A" & chart_range1 & ":A" & chart_range2 & ",l" & chart_range1 & " :m" & chart_range2

Sheets("Chart1").Select
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("yoursheet").Range( _
range_name1), PlotBy:=xlColumns

End Sub