Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting the Values Property of the Series Class
This has stumped me for three days now. I need to be
able to dynamically set the XValues and the (Y)Values for a graph through VBA. I am getting a run -time error 1004 stating it is unable to set the XValues Property of the Series Class. ANYONE PLEEZ! I NEED KUNG FOO LESSONS FROM THE MASTER... Compiler takes me to this line when it errors... ActiveChart.SeriesCollection(1).XValues = XValues Which is related to this line... XValues = "=Query!R" & CStr(x1) & "C1:R" & CStr(x2) & "C1" There must be a better way to set the SeriesCollection (1).XValues, but a macro uses this =Query! statement but it only seems to work for static numbers and I have tried to slip in my variable 'XVALUES'????????????????? HELP!!!! HERE IS THE WHOLE RELEVANT BLOCK OF CODE: x1 = IVSLH2Range.Row + 1 If IVSLH2Range.Offset(intRowCountIVSLH2 - 1, 2).Value < Year(Date) Then x2 = IVSLH2Range.Row + intRowCountIVSLH2 - 1 Else x2 = IVSLH2Range.Row + intRowCountIVSLH2 - 2 End If 'Update Graph Variables intRowCountIVSLH2 2 Then XValues = "=Query!R" & CStr(x1) & "C1:R" & CStr(x2) & "C1" YValues = "=Query!R" & CStr(x1) & "C5:R" & CStr(x2) & "C5" 'Update Graph2 Sheets("Chart2").Select ActiveChart.PlotArea.Select ActiveChart.SeriesCollection(1).XValues = XValues ActiveChart.SeriesCollection(1).Values = YValues With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = strCompanyName End With ActiveChart.ChartArea.Select |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting the Values Property of the Series Class
Build your dynamic range by using a range object.
Something like this: Dim XValues As Range x1 = 2 x2 = 6 With ActiveSheet Set XValues = .Range(.Cells(x1, 2).Address, .Cells (x2, 2).Address) End With ActiveChart.SeriesCollection(1).XValues = XValues I don't think you can use Excel formulas as VBA Object properties. When you enter the formula in Excel, I believe it converts it to a range. Either way, that code should work. HTH. -Brad -----Original Message----- This has stumped me for three days now. I need to be able to dynamically set the XValues and the (Y)Values for a graph through VBA. I am getting a run -time error 1004 stating it is unable to set the XValues Property of the Series Class. ANYONE PLEEZ! I NEED KUNG FOO LESSONS FROM THE MASTER... Compiler takes me to this line when it errors... ActiveChart.SeriesCollection(1).XValues = XValues Which is related to this line... XValues = "=Query!R" & CStr(x1) & "C1:R" & CStr(x2) & "C1" There must be a better way to set the SeriesCollection (1).XValues, but a macro uses this =Query! statement but it only seems to work for static numbers and I have tried to slip in my variable 'XVALUES'????????????????? HELP!!!! HERE IS THE WHOLE RELEVANT BLOCK OF CODE: x1 = IVSLH2Range.Row + 1 If IVSLH2Range.Offset(intRowCountIVSLH2 - 1, 2).Value < Year(Date) Then x2 = IVSLH2Range.Row + intRowCountIVSLH2 - 1 Else x2 = IVSLH2Range.Row + intRowCountIVSLH2 - 2 End If 'Update Graph Variables intRowCountIVSLH2 2 Then XValues = "=Query!R" & CStr(x1) & "C1:R" & CStr(x2) & "C1" YValues = "=Query!R" & CStr(x1) & "C5:R" & CStr(x2) & "C5" 'Update Graph2 Sheets("Chart2").Select ActiveChart.PlotArea.Select ActiveChart.SeriesCollection(1).XValues = XValues ActiveChart.SeriesCollection(1).Values = YValues With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = strCompanyName End With ActiveChart.ChartArea.Select . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting the Values Property of the Series Class
Check out the response in the charting group.
- Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Alex A wrote: This has stumped me for three days now. I need to be able to dynamically set the XValues and the (Y)Values for a graph through VBA. I am getting a run -time error 1004 stating it is unable to set the XValues Property of the Series Class. ANYONE PLEEZ! I NEED KUNG FOO LESSONS FROM THE MASTER... Compiler takes me to this line when it errors... ActiveChart.SeriesCollection(1).XValues = XValues Which is related to this line... XValues = "=Query!R" & CStr(x1) & "C1:R" & CStr(x2) & "C1" There must be a better way to set the SeriesCollection (1).XValues, but a macro uses this =Query! statement but it only seems to work for static numbers and I have tried to slip in my variable 'XVALUES'????????????????? HELP!!!! HERE IS THE WHOLE RELEVANT BLOCK OF CODE: x1 = IVSLH2Range.Row + 1 If IVSLH2Range.Offset(intRowCountIVSLH2 - 1, 2).Value < Year(Date) Then x2 = IVSLH2Range.Row + intRowCountIVSLH2 - 1 Else x2 = IVSLH2Range.Row + intRowCountIVSLH2 - 2 End If 'Update Graph Variables intRowCountIVSLH2 2 Then XValues = "=Query!R" & CStr(x1) & "C1:R" & CStr(x2) & "C1" YValues = "=Query!R" & CStr(x1) & "C5:R" & CStr(x2) & "C5" 'Update Graph2 Sheets("Chart2").Select ActiveChart.PlotArea.Select ActiveChart.SeriesCollection(1).XValues = XValues ActiveChart.SeriesCollection(1).Values = YValues With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = strCompanyName End With ActiveChart.ChartArea.Select |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unable to set the values property of the series class | Excel Discussion (Misc queries) | |||
Unable to set the XValues property of the Series class | Charts and Charting in Excel | |||
Unable to set the Values property of the Series class | Charts and Charting in Excel | |||
Unable to set the XValues property of the Series class | Charts and Charting in Excel | |||
VBA error: Unable to set the Values property of the Series class | Charts and Charting in Excel |