Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble creating a chart
I am trying to create a chart with multiple series in the active worksheet. I
am trying to plot up column A vs B for each series (The Series Name is in column C). Each series is of varying length, so I loop through the list name (column C) and compare it to a short set of Series Names in Column F. I am getting errors with the Set NewSrs statement. Do I have the wrong variable types defined? Any help would be appreciated. Dim i, j, k, l As Long Dim sName, cName, seName As String Dim oChart As Chart Dim NewSrs As Series sName = ActiveSheet.Name Set oChart = Charts.Add oChart.ChartType = xlXYScatter oChart.Location Whe=xlLocationAsObject, Name:=sName k = 0 For i = 1 To Range(Sheets(sName).Range("C1"), Sheets(sName).Range("C1").End(xlDown)).Count If Sheets(sName).Range("C1").Offset(i, 0) = Sheets(sName).Range("F1").Offset(k, 0) Then j = j + 1 Else Set NewSrs = oChart.SeriesCollection.NewSeries With NewSrs ' Name of the first set of data .Name = Sheets(sName).Range("F1").Offset(k, 0).Value ' X-Values in the 1st column .XValues = Sheets(sName).Range(Cells(l + 1, 2), Cells(j , 2)) ' Y-Values in the 2nd column .Values = Sheets(sName).Range(Cells(l + 1, 3), Cells(j , 3)) End With j = j + 1 k = k + 1 End If Next i |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble creating a chart
oChart is the chart sheet, which you destroyed when you converted the chart
to an embedded chart. Use this syntax instead: Set oChart = Sheets(sName).ChartObjects.Add(100, 100, 350, 275).Chart where the numbers in parens are the Left, Top, Width, and Height dimensions of the chart object. You can use whatever values you want, and even set them according to a range you wish to cover: With Sheets(sName).Range("D4:M24") Set oChart = Sheets(sName).ChartObjects.Add(.Left, .Top, .Width, ..Height).Chart End With You should also note that when you Dim a list of variables on one line, each requires the variable type, like this: Dim i As Long, j As Long, k As Long, l As Long In this statement, i, j, and k are all declared as variants, since you didn't specify a variable type for each: Dim i, j, k, l As Long - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "jdh" wrote in message ... I am trying to create a chart with multiple series in the active worksheet. I am trying to plot up column A vs B for each series (The Series Name is in column C). Each series is of varying length, so I loop through the list name (column C) and compare it to a short set of Series Names in Column F. I am getting errors with the Set NewSrs statement. Do I have the wrong variable types defined? Any help would be appreciated. Dim i, j, k, l As Long Dim sName, cName, seName As String Dim oChart As Chart Dim NewSrs As Series sName = ActiveSheet.Name Set oChart = Charts.Add oChart.ChartType = xlXYScatter oChart.Location Whe=xlLocationAsObject, Name:=sName k = 0 For i = 1 To Range(Sheets(sName).Range("C1"), Sheets(sName).Range("C1").End(xlDown)).Count If Sheets(sName).Range("C1").Offset(i, 0) = Sheets(sName).Range("F1").Offset(k, 0) Then j = j + 1 Else Set NewSrs = oChart.SeriesCollection.NewSeries With NewSrs ' Name of the first set of data .Name = Sheets(sName).Range("F1").Offset(k, 0).Value ' X-Values in the 1st column .XValues = Sheets(sName).Range(Cells(l + 1, 2), Cells(j , 2)) ' Y-Values in the 2nd column .Values = Sheets(sName).Range(Cells(l + 1, 3), Cells(j , 3)) End With j = j + 1 k = k + 1 End If Next i |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble creating a chart
Thanks for the help. Everything is working great now.
"Jon Peltier" wrote: oChart is the chart sheet, which you destroyed when you converted the chart to an embedded chart. Use this syntax instead: Set oChart = Sheets(sName).ChartObjects.Add(100, 100, 350, 275).Chart where the numbers in parens are the Left, Top, Width, and Height dimensions of the chart object. You can use whatever values you want, and even set them according to a range you wish to cover: With Sheets(sName).Range("D4:M24") Set oChart = Sheets(sName).ChartObjects.Add(.Left, .Top, .Width, ..Height).Chart End With You should also note that when you Dim a list of variables on one line, each requires the variable type, like this: Dim i As Long, j As Long, k As Long, l As Long In this statement, i, j, and k are all declared as variants, since you didn't specify a variable type for each: Dim i, j, k, l As Long - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "jdh" wrote in message ... I am trying to create a chart with multiple series in the active worksheet. I am trying to plot up column A vs B for each series (The Series Name is in column C). Each series is of varying length, so I loop through the list name (column C) and compare it to a short set of Series Names in Column F. I am getting errors with the Set NewSrs statement. Do I have the wrong variable types defined? Any help would be appreciated. Dim i, j, k, l As Long Dim sName, cName, seName As String Dim oChart As Chart Dim NewSrs As Series sName = ActiveSheet.Name Set oChart = Charts.Add oChart.ChartType = xlXYScatter oChart.Location Whe=xlLocationAsObject, Name:=sName k = 0 For i = 1 To Range(Sheets(sName).Range("C1"), Sheets(sName).Range("C1").End(xlDown)).Count If Sheets(sName).Range("C1").Offset(i, 0) = Sheets(sName).Range("F1").Offset(k, 0) Then j = j + 1 Else Set NewSrs = oChart.SeriesCollection.NewSeries With NewSrs ' Name of the first set of data .Name = Sheets(sName).Range("F1").Offset(k, 0).Value ' X-Values in the 1st column .XValues = Sheets(sName).Range(Cells(l + 1, 2), Cells(j , 2)) ' Y-Values in the 2nd column .Values = Sheets(sName).Range(Cells(l + 1, 3), Cells(j , 3)) End With j = j + 1 k = k + 1 End If Next i |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble creating table | Excel Discussion (Misc queries) | |||
Trouble creating DSN-less connection | Excel Programming | |||
chart trouble | Charts and Charting in Excel | |||
trouble w/ creating a VBA function that uses an enum in it's parameters | Excel Programming | |||
having trouble creating chart to show monthly totals | Charts and Charting in Excel |