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.
|