Pierre has attended:
Excel VBA Intro Intermediate course
VBA Create chart
Hi,
i manage to create one chart with the following line:
.SetSourceData Source:=Sheets("Sheet1").Range("B2:I76")
I want to create several graph with different data zone in the same CurrentRegion (So, I cannot basically use CurrentRegion), I tried
using variable and loop with:
Sub Creategraph()
Dim intColumnCount As Integer
Dim intRowCount As Integer
Dim intEndData As Integer
Dim aChart As Chart
For intRowCount = 1 To Sheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
If Sheets("Sheet1").Cells(intRowCount, 1).Value = "Date / Time" Then
Set aChart = Charts.Add
intEndData = intRowCount + 72
With aChart
.ChartType = xlXYScatterSmooth
.SetSourceData Source:=Sheets("Sheet1").Range(Cells(intRowCount, 2), Cells(intEndData, 9))
.HasTitle = True
.ChartTitle.Text = "Reactor 1"
End With
End If
Next intRowCount
End Sub
RE: VBA Create chart
Hi Pierre
Thanks for your question
It is difficult to advise you effectively without seeing the data. Could you recreate the layout of the chart data, and indicate the different ways in which you want it charted. If you create a simple spreadsheet and email it to
sw@stl-training.co.uk
I will look into the problem today
Regards
Stephen
RE: VBA Create chart
I have just sent an Email to you, thanks for resolving this. If you have any question reply to my Email.
Regards,
Pierre
RE: VBA Create chart
Hi Pierre
Thanks for the file
This is something of a puzzle, as the error seems to make no sense, in that it seems to successfully create the chart.
Can I suggest that you place at the very top of your procedure the line
On Error Resume NextThis will ignore the error message and will carry on running the code. I have done this on the file you sent me and the charts all seem to be created and populated. I need you to check to see if the results are accurate
This is not a solution as we need to find the reason for the error, but this will help narrow down the possibilities
Regards
Stephen
RE: VBA Create chart
Hi,
I don't know if you are still working on this but I managed to solve this issue by adding this line:
Set aChart = aChart.Location(Where:=xlLocationAsObject, Name:="Sheet1")
This is creating a chart in Sheet 1 and after I put this line to move it at the end :
.Location Where:=xlLocationAsNewSheet
Apparently that is solving the issue and I don't need anymore On Error Resume Next.
Regards,
Pierre
