PDA

View Full Version : Chart code problem



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!

Bob Phillips
12-11-2008, 07:23 AM
Try this



chrGraf.SetSourceData Source:=Range("A1:D1").Resize(Application.CountA(Columns(1)))

Bob Phillips
12-11-2008, 02:19 PM
The suggestion you gave to my problem (chart code) :
chrGraf.SetSourceData Source:=Range("A1.D1").Resize(Application.CountA(Columns(1)))

Is not going to work because the range for your solution are all the used cells in column A, but the range I need for my chart are the 2 inner columns of a total of 4 columns. In other words if I have data from ("A1.D20") I would need the numbers that are ("B5:C20")...

I have thought about using "UsedRange" and then "Resize", but is it possible with UsedRange to cut down on the number of columns or only on the number of rows?

Please don't continue the thread by PM, that defeats the purpose of the forum.



chrGraf.SetSourceData Source:=Range("B1:C1").Resize(Application.CountA(Columns(1)))