Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting rows to an empty cell and making a chart
I'm stuck with the next problem. I have two rows of data that look
like this: 0 97.7 1 20 95.6 1 40 89.6 1 60 89.0 1 0 90.4 2 20 90.7 2 40 90.9 2 60 89.9 2 80 88.4 2 0 89.8 3 .... .... and this goes on to 160 What I want to make charts with this data. Like on the x-axis the data in the left columnt and on Y-axis data from the right column. So this goes pretty easy with this: Charts.Add ActiveChart.ChartType = xlXYScatterSmooth ActiveChart.SeriesCollection(1).XValues = "='TEMPLATE'!R6C6:R17C6" ActiveChart.SeriesCollection(1).Values = "='TEMPLATE'!R6C15:R17C15" ActiveChart.SeriesCollection(1).name = "='TEMPLATE'!R6C7" etc... Now, is there a way to make this go automatically? So that the macro finds the first empty cell in the row, makes a chart with the data, gives the chart a name, and then goes on to the next set of data and makes a new chart...? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting rows to an empty cell and making a chart
Nick -
This isn't exactly what you're going to need, because it just uses the column A data for the X values, and B for the Y, plus the first cell in column C in that block of data for the series name. Your code uses columns 6, 15, and 7 for this. But it steps down to each block, and uses just that block for the next chart. 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) Set myChartObject = ActiveSheet.ChartObjects.Add _ (myCell.Offset(0, 3).Left, myCell.Top, 350, 275) 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) End With Set myCell = myCell2.End(xlDown) Debug.Print myCell.Address Loop Until myCell.Row = 65536 End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Nick wrote: I'm stuck with the next problem. I have two rows of data that look like this: 0 97.7 1 20 95.6 1 40 89.6 1 60 89.0 1 0 90.4 2 20 90.7 2 40 90.9 2 60 89.9 2 80 88.4 2 0 89.8 3 .... .... and this goes on to 160 What I want to make charts with this data. Like on the x-axis the data in the left columnt and on Y-axis data from the right column. So this goes pretty easy with this: Charts.Add ActiveChart.ChartType = xlXYScatterSmooth ActiveChart.SeriesCollection(1).XValues = "='TEMPLATE'!R6C6:R17C6" ActiveChart.SeriesCollection(1).Values = "='TEMPLATE'!R6C15:R17C15" ActiveChart.SeriesCollection(1).name = "='TEMPLATE'!R6C7" etc... Now, is there a way to make this go automatically? So that the macro finds the first empty cell in the row, makes a chart with the data, gives the chart a name, and then goes on to the next set of data and makes a new chart...? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting rows to an empty cell and making a chart
Jon, thank you very much. I guess I should get a bit more into VBA.
Anyways, this works very well, but I want to make some changes. Because I always have four sets of data I would like to put them all into one chart. As mentioned before, data look something like this: 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 0 98,5 4 So I changed your code into this: "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 this works fine as long as I only make one chart. After that the program is stuck. Any idea why? And I also had an other question. How can I give a name to the chart? Something like: With ActiveChart ..HasTitle = True ..ChartTitle.Text = "Blabla" End With doesn't work. Thanx in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting rows to an empty cell and making a chart
Hi Nick -
Check out my response to your more recent post. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Nick wrote: Jon, thank you very much. I guess I should get a bit more into VBA. Anyways, this works very well, but I want to make some changes. Because I always have four sets of data I would like to put them all into one chart. As mentioned before, data look something like this: 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 0 98,5 4 So I changed your code into this: "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 this works fine as long as I only make one chart. After that the program is stuck. Any idea why? And I also had an other question. How can I give a name to the chart? Something like: With ActiveChart ..HasTitle = True ..ChartTitle.Text = "Blabla" End With doesn't work. Thanx in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making a cell empty | Excel Discussion (Misc queries) | |||
Making a cell blank or empty without deleting it | New Users to Excel | |||
How can Excel make a cell empty without making it na()? | Excel Discussion (Misc queries) | |||
Function making cell really "empty" | Excel Worksheet Functions | |||
counting non-empty rows in a column | Excel Programming |