Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro problem: range offset question | Excel Discussion (Misc queries) | |||
Range R1C1 notation & the problem with my Macro | Excel Programming | |||
Problem with named range as VBA macro parameter | Excel Programming | |||
Excel Macro Problem, Add-in need to work in every workbook & Error:9 Subscript out of range | Excel Programming |