Macro for Graph - problem with Range
Could someone please help me with the following code
Because my list changes with new entries I need the macro to identify the correct range. I did try substituting Range("B10:G13") with .ActiveCell and all combinations I have tried receive the following error Run-time error '438' Object doesn't support this property or method Is there another line I need to add to this code for it to recognise the end of the data table? Sub Macro9() ' ' Macro9 Macro ' Macro recorded 3/03/2007 by Tanya ' ' Range("Header").Select Charts.Add ActiveChart.ChartType = xlLineMarkersStacked 'Difficulty with next line of code - problem with Range as area changes with new entries in list ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B10:G13"), PlotBy _ :=xlRows ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = "Daily Hours Worked" .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Day of Week" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Hours worked" End With With ActiveChart .HasAxis(xlCategory, xlPrimary) = True .HasAxis(xlValue, xlPrimary) = True End With ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic With ActiveChart.Axes(xlCategory) .HasMajorGridlines = False .HasMinorGridlines = False End With With ActiveChart.Axes(xlValue) .HasMajorGridlines = True .HasMinorGridlines = False End With ActiveChart.HasDataTable = False End Sub |
Macro for Graph - problem with Range
Add these two lines before Range("Header").select:
Dim chartrange As Range Set chartrange = Range(Range("B10"), Range("G10").End(xlDown)) Then, adjust your problem statement with the following line: ActiveChart.SetSourceData Source:=chartrange, PlotBy:=xlRows -- Jay "Tanya" wrote: Could someone please help me with the following code Because my list changes with new entries I need the macro to identify the correct range. I did try substituting Range("B10:G13") with .ActiveCell and all combinations I have tried receive the following error Run-time error '438' Object doesn't support this property or method Is there another line I need to add to this code for it to recognise the end of the data table? Sub Macro9() ' ' Macro9 Macro ' Macro recorded 3/03/2007 by Tanya ' ' Range("Header").Select Charts.Add ActiveChart.ChartType = xlLineMarkersStacked 'Difficulty with next line of code - problem with Range as area changes with new entries in list ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B10:G13"), PlotBy _ :=xlRows ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = "Daily Hours Worked" .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Day of Week" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Hours worked" End With With ActiveChart .HasAxis(xlCategory, xlPrimary) = True .HasAxis(xlValue, xlPrimary) = True End With ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic With ActiveChart.Axes(xlCategory) .HasMajorGridlines = False .HasMinorGridlines = False End With With ActiveChart.Axes(xlValue) .HasMajorGridlines = True .HasMinorGridlines = False End With ActiveChart.HasDataTable = False End Sub |
Macro for Graph - problem with Range
Thank you very much Jay, it worked a charm.
Kind Regards Tanya "Jay" wrote: Add these two lines before Range("Header").select: Dim chartrange As Range Set chartrange = Range(Range("B10"), Range("G10").End(xlDown)) Then, adjust your problem statement with the following line: ActiveChart.SetSourceData Source:=chartrange, PlotBy:=xlRows -- Jay "Tanya" wrote: Could someone please help me with the following code Because my list changes with new entries I need the macro to identify the correct range. I did try substituting Range("B10:G13") with .ActiveCell and all combinations I have tried receive the following error Run-time error '438' Object doesn't support this property or method Is there another line I need to add to this code for it to recognise the end of the data table? Sub Macro9() ' ' Macro9 Macro ' Macro recorded 3/03/2007 by Tanya ' ' Range("Header").Select Charts.Add ActiveChart.ChartType = xlLineMarkersStacked 'Difficulty with next line of code - problem with Range as area changes with new entries in list ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B10:G13"), PlotBy _ :=xlRows ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1" With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = "Daily Hours Worked" .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Day of Week" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Hours worked" End With With ActiveChart .HasAxis(xlCategory, xlPrimary) = True .HasAxis(xlValue, xlPrimary) = True End With ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic With ActiveChart.Axes(xlCategory) .HasMajorGridlines = False .HasMinorGridlines = False End With With ActiveChart.Axes(xlValue) .HasMajorGridlines = True .HasMinorGridlines = False End With ActiveChart.HasDataTable = False End Sub |
All times are GMT +1. The time now is 02:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com