Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does't this work? (chart in excel)
I have columns with data that look something like this: A B C 0 98.7 1 20 99.4 1 0 98.6 2 20 98.7 2 0 98.2 3 20 99.5 3 0 97.3 4 20 98.5 4 and then again 0 89.9 1 20 90.3 1 etc... And what I want to do is to make charts with this data with data fro column A on the x-Axis and column B on the Y-axis. Column C is used fo the name of the range. Because there are always 4 sets of data (column C 1 to 4) I would lik to put those four into one chart and then go on and make an other char etc... So I have this code, but the problem is it get's stuck afte the first chart has been made. Can anyone say way? Sub MakeCharts() Dim myCell As Range Dim myCell2 As Range Dim myRange As Range Dim myChartObject As ChartObject Dim mySeries As Series Dim counter As Single counter = 0 Set myCell = ActiveSheet.Cells(1, 1) Debug.Print myCell.Address If Len(myCell.Text) = 0 Then Set myCell = myCell.End(xlDown) Debug.Print myCell.Address End If Do Set myChartObject = ActiveSheet.ChartObjects.Add _ (myCell.Offset(0, 3).Left, myCell.Top, 350, 275) Do While counter < 4 Set myCell2 = myCell.End(xlDown) Set myRange = ActiveSheet.Range(myCell, myCell2) Set mySeries = myChartObject.Chart.SeriesCollection.NewSeries With mySeries .Values = myRange.Offset(0, 1) .XValues = myRange .Name = "=" & myRange.Offset(0, 2).Resize(1, 1).Addres _(ReferenceStyle:=xlR1C1, external:=True) .ChartType = xlXYScatterSmooth End With Set myCell = myCell2.End(xlDown) Debug.Print myCell.Address counter = counter + 1 Loop Loop Until myCell.Row = 65536 End Sub And my other question is how can I give a name to those charts? Spmething like: With ActiveChart .HasTitle = True .ChartTitle.Text = "Blabla" End With doesn't work ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does't this work? (chart in excel)
1. I'd switch it around a bit, find out what the value in C is, and if
it's 1, I'd add a new chart. After I adjusted your adjustments of my code, I noticed you'd removed the dot in front of Values, XValues, and Name between With mySeries/End With when assigning these parameters to the new series. Sub MakeCharts() Dim myCell As Range Dim myCell2 As Range Dim myRange As Range Dim myChartObject As ChartObject Dim mySeries As Series Set myCell = ActiveSheet.Cells(1, 1) Debug.Print myCell.Address If Len(myCell.Text) = 0 Then Set myCell = myCell.End(xlDown) Debug.Print myCell.Address End If Do Set myCell2 = myCell.End(xlDown) Set myRange = ActiveSheet.Range(myCell, myCell2) If myCell.Offset(0, 2).Value = 1 Then '' make a new chart Set myChartObject = ActiveSheet.ChartObjects.Add _ (myCell.Offset(0, 3).Left, myCell.Top, 350, 275) End If Set mySeries = myChartObject.Chart.SeriesCollection.NewSeries With mySeries .Values = myRange.Offset(0, 1) .XValues = myRange .Name = "=" & myRange.Offset(0, 2).Resize(1, 1).Address _ (ReferenceStyle:=xlR1C1, external:=True) .ChartType = xlXYScatterSmooth End With Set myCell = myCell2.End(xlDown) Debug.Print myCell.Address Loop Until myCell.Row = 65536 End Sub 2. You need to change the name of the chart object which contains the chart. With ActiveChart .Parent.Name = "Yada Yada" End With - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ dreamer wrote: I have columns with data that look something like this: A B C 0 98.7 1 20 99.4 1 0 98.6 2 20 98.7 2 0 98.2 3 20 99.5 3 0 97.3 4 20 98.5 4 and then again 0 89.9 1 20 90.3 1 etc... And what I want to do is to make charts with this data with data from column A on the x-Axis and column B on the Y-axis. Column C is used for the name of the range. Because there are always 4 sets of data (column C 1 to 4) I would like to put those four into one chart and then go on and make an other chart etc... So I have this code, but the problem is it get's stuck after the first chart has been made. Can anyone say way? Sub MakeCharts() Dim myCell As Range Dim myCell2 As Range Dim myRange As Range Dim myChartObject As ChartObject Dim mySeries As Series Dim counter As Single counter = 0 Set myCell = ActiveSheet.Cells(1, 1) Debug.Print myCell.Address If Len(myCell.Text) = 0 Then Set myCell = myCell.End(xlDown) Debug.Print myCell.Address End If Do Set myChartObject = ActiveSheet.ChartObjects.Add _ (myCell.Offset(0, 3).Left, myCell.Top, 350, 275) Do While counter < 4 Set myCell2 = myCell.End(xlDown) Set myRange = ActiveSheet.Range(myCell, myCell2) Set mySeries = myChartObject.Chart.SeriesCollection.NewSeries With mySeries .Values = myRange.Offset(0, 1) .XValues = myRange .Name = "=" & myRange.Offset(0, 2).Resize(1, 1).Address _(ReferenceStyle:=xlR1C1, external:=True) .ChartType = xlXYScatterSmooth End With Set myCell = myCell2.End(xlDown) Debug.Print myCell.Address counter = counter + 1 Loop Loop Until myCell.Row = 65536 End Sub And my other question is how can I give a name to those charts? Spmething like: With ActiveChart .HasTitle = True .ChartTitle.Text = "Blabla" End With doesn't work. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can not insert chart in Excel 2007? Chart does not work right? | Excel Discussion (Misc queries) | |||
Sorting in an Excel Chart won't work properly | Excel Discussion (Misc queries) | |||
Double Clicking on link w/i workbook does't bring me to souce. | Excel Worksheet Functions | |||
Excel 12, insert chart does not work. Exel restart. Why? | Charts and Charting in Excel | |||
How do I activate the trial exel 2003 program if it does't find c. | New Users to Excel |