| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Source Range For Chart
I have this macro basically done, I am having a problem with settign the source range for the chart though. I think it is simply a syntax error but I am not sure - any advice would be helpful as I am stuck. Here is the part of the code
Dim LastDataRow As Lon LastDataRow = Cells(Rows.Count, "C").End(xlUp).Ro If LastDataRow = 1 Then GoTo MyErrorHandle Els Sheets("InputSheet").Visible = Fals Sheets("Chart1").Visible = Tru Sheets("Chart1").Selec With ActiveChar .SetSourceData Source:=Workbooks("Main.xls").Sheets("DataSheet"). Range("C1", Range("H" & LastDataRow)), PlotBy:=xlColumn .HasTitle = Tru .ChartTitle.Characters.Text = "Chart .Axes(xlValue, xlPrimary).HasTitle = Tru .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Percentage .HasAxis(xlCategory, xlPrimary) = Tru .HasAxis(xlValue, xlPrimary) = Tru .Axes(xlCategory, xlPrimary).CategoryType = xlAutomati .HasLegend = Fals .HasDataTable = Tru .DataTable.ShowLegendKey = Tru End Wit End I Exit Su MyErrorHandler '"Whole bunch of stuff here End Su I am getting the error "Mehtod 'Range' of Object '_Global' Failed" on the .SetSourceData command. And I get some problems with my "if" and "else" statements. What am I doing wrong - looking at it, it should work, I just don't know where I am going wrong Thanks for any advice Jim |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Setting Source Range For Chart
Hi James,
The problem is that the use of Range("H" & LastDataRow) will fail because the activesheet is a chart sheet not a worksheet. So try this small modification. ..SetSourceData Source:= _ ThisWorkbook.Sheets("Sheet2").Range("C1", "H" & CStr(LastDataRow)), _ PlotBy:=xlColumns Cheers Andy James Stephens wrote: I have this macro basically done, I am having a problem with settign the source range for the chart though. I think it is simply a syntax error but I am not sure - any advice would be helpful as I am stuck. Here is the part of the code: Dim LastDataRow As Long LastDataRow = Cells(Rows.Count, "C").End(xlUp).Row If LastDataRow = 1 Then GoTo MyErrorHandler Else Sheets("InputSheet").Visible = False Sheets("Chart1").Visible = True Sheets("Chart1").Select With ActiveChart .SetSourceData Source:=Workbooks("Main.xls").Sheets("DataSheet"). Range("C1", Range("H" & LastDataRow)), _ PlotBy:=xlColumns .HasTitle = True .ChartTitle.Characters.Text = "Chart" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Percentage" .HasAxis(xlCategory, xlPrimary) = True .HasAxis(xlValue, xlPrimary) = True .Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic .HasLegend = False .HasDataTable = True .DataTable.ShowLegendKey = True End With End If Exit Sub MyErrorHandler: '"Whole bunch of stuff here" End Sub I am getting the error "Mehtod 'Range' of Object '_Global' Failed" on the .SetSourceData command. And I get some problems with my "if" and "else" statements. What am I doing wrong - looking at it, it should work, I just don't know where I am going wrong. Thanks for any advice, Jim -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Setting Source Range For Chart
Try it like this:
Dim LastDataRow As Long LastDataRow = Cells(Rows.Count, "C").End(xlUp).Row If LastDataRow = 1 Then GoTo MyErrorHandler Else Sheets("InputSheet").Visible = False Sheets("Chart1").Visible = True Sheets("Chart1").Select With ActiveChart Set Sh = Workbooks("Main.xls").Sheets("DataSheet") .SetSourceData Source:=Workbooks("Main.xls"). _ Sh.Range(Sh.Range("C1"), Sh.Range("H" & LastDataRow)), _ PlotBy:=xlColumns .HasTitle = True .ChartTitle.Characters.Text = "Chart" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Percentage" .HasAxis(xlCategory, xlPrimary) = True .HasAxis(xlValue, xlPrimary) = True .Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic .HasLegend = False .HasDataTable = True .DataTable.ShowLegendKey = True End With End If Exit Sub MyErrorHandler: '"Whole bunch of stuff here" End Sub -- Regards, Tom Ogilvy "James Stephens" wrote in message ... I have this macro basically done, I am having a problem with settign the source range for the chart though. I think it is simply a syntax error but I am not sure - any advice would be helpful as I am stuck. Here is the part of the code: Dim LastDataRow As Long LastDataRow = Cells(Rows.Count, "C").End(xlUp).Row If LastDataRow = 1 Then GoTo MyErrorHandler Else Sheets("InputSheet").Visible = False Sheets("Chart1").Visible = True Sheets("Chart1").Select With ActiveChart .SetSourceData Source:=Workbooks("Main.xls").Sheets("DataSheet"). Range("C1", Range("H" & LastDataRow)), _ PlotBy:=xlColumns .HasTitle = True .ChartTitle.Characters.Text = "Chart" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Percentage" .HasAxis(xlCategory, xlPrimary) = True .HasAxis(xlValue, xlPrimary) = True .Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic .HasLegend = False .HasDataTable = True .DataTable.ShowLegendKey = True End With End If Exit Sub MyErrorHandler: '"Whole bunch of stuff here" End Sub I am getting the error "Mehtod 'Range' of Object '_Global' Failed" on the ..SetSourceData command. And I get some problems with my "if" and "else" statements. What am I doing wrong - looking at it, it should work, I just don't know where I am going wrong. Thanks for any advice, Jim |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Setting Source Range For Chart
Whoops left in some residual code that should have been removed. : Dim LastDataRow As Long LastDataRow = Cells(Rows.Count, "C").End(xlUp).Row If LastDataRow = 1 Then GoTo MyErrorHandler Else Sheets("InputSheet").Visible = False Sheets("Chart1").Visible = True Sheets("Chart1").Select Set Sh = Workbooks("Main.xls").Sheets("DataSheet") With ActiveChart .SetSourceData Source:= Sh.Range(Sh.Range("C1"), _ Sh.Range("H" & LastDataRow)), _ PlotBy:=xlColumns .HasTitle = True .ChartTitle.Characters.Text = "Chart" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Percentage" .HasAxis(xlCategory, xlPrimary) = True .HasAxis(xlValue, xlPrimary) = True .Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic .HasLegend = False .HasDataTable = True .DataTable.ShowLegendKey = True End With End If Exit Sub MyErrorHandler: '"Whole bunch of stuff here" End Sub -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Try it like this: Dim LastDataRow As Long LastDataRow = Cells(Rows.Count, "C").End(xlUp).Row If LastDataRow = 1 Then GoTo MyErrorHandler Else Sheets("InputSheet").Visible = False Sheets("Chart1").Visible = True Sheets("Chart1").Select With ActiveChart Set Sh = Workbooks("Main.xls").Sheets("DataSheet") .SetSourceData Source:=Workbooks("Main.xls"). _ Sh.Range(Sh.Range("C1"), Sh.Range("H" & LastDataRow)), _ PlotBy:=xlColumns .HasTitle = True .ChartTitle.Characters.Text = "Chart" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Percentage" .HasAxis(xlCategory, xlPrimary) = True .HasAxis(xlValue, xlPrimary) = True .Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic .HasLegend = False .HasDataTable = True .DataTable.ShowLegendKey = True End With End If Exit Sub MyErrorHandler: '"Whole bunch of stuff here" End Sub -- Regards, Tom Ogilvy "James Stephens" wrote in message ... I have this macro basically done, I am having a problem with settign the source range for the chart though. I think it is simply a syntax error but I am not sure - any advice would be helpful as I am stuck. Here is the part of the code: Dim LastDataRow As Long LastDataRow = Cells(Rows.Count, "C").End(xlUp).Row If LastDataRow = 1 Then GoTo MyErrorHandler Else Sheets("InputSheet").Visible = False Sheets("Chart1").Visible = True Sheets("Chart1").Select With ActiveChart .SetSourceData Source:=Workbooks("Main.xls").Sheets("DataSheet"). Range("C1", Range("H" & LastDataRow)), _ PlotBy:=xlColumns .HasTitle = True .ChartTitle.Characters.Text = "Chart" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Percentage" .HasAxis(xlCategory, xlPrimary) = True .HasAxis(xlValue, xlPrimary) = True .Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic .HasLegend = False .HasDataTable = True .DataTable.ShowLegendKey = True End With End If Exit Sub MyErrorHandler: '"Whole bunch of stuff here" End Sub I am getting the error "Mehtod 'Range' of Object '_Global' Failed" on the .SetSourceData command. And I get some problems with my "if" and "else" statements. What am I doing wrong - looking at it, it should work, I just don't know where I am going wrong. Thanks for any advice, Jim |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Setting Source Range For Chart
Thanks, seems to work great, and now I understand where my problem was
Greatly appreciate the help Ji ----- Tom Ogilvy wrote: ---- Whoops left in some residual code that should have been removed. Dim LastDataRow As Lon LastDataRow = Cells(Rows.Count, "C").End(xlUp).Ro If LastDataRow = 1 The GoTo MyErrorHandle Els Sheets("InputSheet").Visible = Fals Sheets("Chart1").Visible = Tru Sheets("Chart1").Selec Set Sh = Workbooks("Main.xls").Sheets("DataSheet" With ActiveChar .SetSourceData Source:= Sh.Range(Sh.Range("C1"), Sh.Range("H" & LastDataRow)), PlotBy:=xlColumn .HasTitle = Tru .ChartTitle.Characters.Text = "Chart .Axes(xlValue, xlPrimary).HasTitle = Tru .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Percentage .HasAxis(xlCategory, xlPrimary) = Tru .HasAxis(xlValue, xlPrimary) = Tru .Axes(xlCategory, xlPrimary).CategoryType = xlAutomati .HasLegend = Fals .HasDataTable = Tru .DataTable.ShowLegendKey = Tru End Wit End I Exit Su MyErrorHandler '"Whole bunch of stuff here End Su -- Regards Tom Ogilv "Tom Ogilvy" wrote in messag .. Try it like this Dim LastDataRow As Lon LastDataRow = Cells(Rows.Count, "C").End(xlUp).Ro If LastDataRow = 1 The GoTo MyErrorHandle Els Sheets("InputSheet").Visible = Fals Sheets("Chart1").Visible = Tru Sheets("Chart1").Selec With ActiveChar Set Sh = Workbooks("Main.xls").Sheets("DataSheet" .SetSourceData Source:=Workbooks("Main.xls"). Sh.Range(Sh.Range("C1"), Sh.Range("H" & LastDataRow)), PlotBy:=xlColumn .HasTitle = Tru .ChartTitle.Characters.Text = "Chart .Axes(xlValue, xlPrimary).HasTitle = Tru .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Percentage .HasAxis(xlCategory, xlPrimary) = Tru .HasAxis(xlValue, xlPrimary) = Tru .Axes(xlCategory, xlPrimary).CategoryType = xlAutomati .HasLegend = Fals .HasDataTable = Tru .DataTable.ShowLegendKey = Tru End Wit End I Exit Su MyErrorHandler '"Whole bunch of stuff here End Su -- Regards Tom Ogilv "James Stephens" wrote in messag .. I have this macro basically done, I am having a problem with settig th source range for the chart though. I think it is simply a syntax erro bu I am not sure - any advice would be helpful as I am stuck. Here is th par of the code Dim LastDataRow As Lon LastDataRow = Cells(Rows.Count, "C").End(xlUp).Ro If LastDataRow = 1 Then GoTo MyErrorHandle Els Sheets("InputSheet").Visible = Fals Sheets("Chart1").Visible = Tru Sheets("Chart1").Selec With ActiveChar .SetSourceDat Source:=Workbooks("Main.xls").Sheets("DataSheet"). Range("C1", Range("H" & LastDataRow)), PlotBy:=xlColumn .HasTitle = Tru .ChartTitle.Characters.Text = "Chart .Axes(xlValue, xlPrimary).HasTitle = Tru .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text "Percentage .HasAxis(xlCategory, xlPrimary) = Tru .HasAxis(xlValue, xlPrimary) = Tru .Axes(xlCategory, xlPrimary).CategoryType = xlAutomati .HasLegend = False .HasDataTable = True .DataTable.ShowLegendKey = True End With End If Exit Sub MyErrorHandler: '"Whole bunch of stuff here" End Sub I am getting the error "Mehtod 'Range' of Object '_Global' Failed" on the .SetSourceData command. And I get some problems with my "if" and "else" statements. What am I doing wrong - looking at it, it should work, I just don't know where I am going wrong. Thanks for any advice, Jim |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using a named range as a data source for a chart | Charts and Charting in Excel | |||
| Problem setting SeriesCollections source range | Charts and Charting in Excel | |||
| Setting source data range with Charts | Charts and Charting in Excel | |||
| Setting the range in a chart using VB | Excel Programming | |||
| change chart source from range to raw data | Excel Programming | |||
Hybrid Mode
