Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

vba create chart

ResolvedVersion 2007

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

Edited on Fri 8 Jul 2011, 09:04

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

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

 

Training courses

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Access tip:

Hide Access Tables

One method of stopping your Access tables appearing in Database window, is to rename the table(s) with the prefix usys.

This converts the table into a system object, which cannot be viewed in the Database window.

You can show system objects in the Database window by the following:

Step 1: On the Tools menu, click Options.
Step 2: Click the View tab.
Under Show, select the System Objects check box.
Click OK.

View all Access hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.12 secs.