Nunu
12-11-2008, 06:52 AM
Hi Everyone!
I'm trying to write a code that will create a graf in Excel, I used the macro-recorder to get an example code but I now have problems addapting it so that I will be able to use it with different sizes of data.
I have the following:
Sub MakeChart()
Dim Chart As Range
Dim xyRange As Variant
Dim i As Integer
Dim Raekker As Integer
Dim chrGraf As Chart
Set Chart = ThisWorkbook.Sheets(3).Range("A1")
Raekker = ThisWorkbook.Sheets(3).UsedRange.Rows.Count
For i = 1 To Raekker
If IsNumeric(Chart.Cells(i, 1)) And Chart.Cells(i, 1) > 0 Then Exit For
Next i
With Chart
xyRange = Range(.Offset(i - 1, 1), .Offset(Raekker, 2))
End With
Set chrGraf = ThisWorkbook.Sheets(3).Shapes.AddChart.Chart
chrGraf.ChartType = xlXYScatter
chrGraf.SetSourceData Source:=Chart.xyRange
chrGraf.ChartStyle = 17
chrGraf.ClearToMatchStyle
chrGraf.Axes(xlValue).Delete
chrGraf.Axes(xlCategory).Delete
chrGraf.ApplyLayout (4)
chrGraf.Legend.Delete
chrGraf.Parent.RoundedCorners = True
End Sub
When I try to run the code it gets an error at the line: chrGraf.SetSourceData Source:=Chart.xyRange
I have also tried writing it as:
chrGraf.SetSourceData Source:=Chart.Range(.Offset(i - 1, 1), .Offset(Raekker, 2))
but that doesn't work either, what am I doing wrong?
Any help is appreciated!
I'm trying to write a code that will create a graf in Excel, I used the macro-recorder to get an example code but I now have problems addapting it so that I will be able to use it with different sizes of data.
I have the following:
Sub MakeChart()
Dim Chart As Range
Dim xyRange As Variant
Dim i As Integer
Dim Raekker As Integer
Dim chrGraf As Chart
Set Chart = ThisWorkbook.Sheets(3).Range("A1")
Raekker = ThisWorkbook.Sheets(3).UsedRange.Rows.Count
For i = 1 To Raekker
If IsNumeric(Chart.Cells(i, 1)) And Chart.Cells(i, 1) > 0 Then Exit For
Next i
With Chart
xyRange = Range(.Offset(i - 1, 1), .Offset(Raekker, 2))
End With
Set chrGraf = ThisWorkbook.Sheets(3).Shapes.AddChart.Chart
chrGraf.ChartType = xlXYScatter
chrGraf.SetSourceData Source:=Chart.xyRange
chrGraf.ChartStyle = 17
chrGraf.ClearToMatchStyle
chrGraf.Axes(xlValue).Delete
chrGraf.Axes(xlCategory).Delete
chrGraf.ApplyLayout (4)
chrGraf.Legend.Delete
chrGraf.Parent.RoundedCorners = True
End Sub
When I try to run the code it gets an error at the line: chrGraf.SetSourceData Source:=Chart.xyRange
I have also tried writing it as:
chrGraf.SetSourceData Source:=Chart.Range(.Offset(i - 1, 1), .Offset(Raekker, 2))
but that doesn't work either, what am I doing wrong?
Any help is appreciated!