Forum home » Delegate support and help forum » Microsoft Access VBA Training and help » VBA Create chart
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
VBA Create chart
Resolved · Urgent Priority · Version 2007
Reply Wed 6 Jul 2011, 16:11Delegate Pierre said...
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
For upcoming training course dates see: Pricing & availability
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
I will look into the problem today
Regards
Stephen
Reply Thu 7 Jul 2011, 14:44Delegate Pierre said...
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 Next
This 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
Reply Wed 13 Jul 2011, 09:34Delegate Pierre said...
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
|