Creating Chart Using a Macro

[paul-bentley]

Hello all,

This is my first post borne out of sheer and utter frustration trying to get to grips with VBA.

I've been dipping in and out of VBA in my job as an Acoustic Engineer trying to devise code to replace a lot of the repetitive tasks in terms of data handling that we do.

As an ongoing project, i'm trying to create a Macro that can create a chart of survey data. Our surveys consists of different number of rows each time as it depends on how long the survey was carried out for. I have the following code (see below) to essentially create the chart irrespective of the size of the data as it supposed to count to the end of the column until it finds a blank cell.

The Cell F5 is the start of the column that defines the X axis, which is the date and the G5 cell is the start of the column where the survey data is in.

When I run this code it only gets to about half way and then stops. Any ideas why this might be?

I got the basic code off another website so I am confident the counter works and the main loop is based on a recording I made to make the chart.

Sub Create_Chart()
Application.ScreenUpdating = False

'Define the Variables
'-------------------------
Dim counter As Long
Dim totcounter As Long


'Initialise the Variables
'-----------------------
counter = 1
totcounter = 1



Sheets("Raw Data Sheet").Select

'Total Counter to tell macro when to stop
'-----------------------------------------
Do Until IsEmpty(Cells(totcounter, 1)) = True
totcounter = totcounter + 1
Loop

totcounter = totcounter - 1



'Main Loop of Programme
'----------------------------------

Range("F5:G5" & totcounter).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Range("'Raw Data Sheet'!$F$5:$G$5" & totcounter)

Application.ScreenUpdating = True

End Sub

[daffodil11]

Please be sure to use CODE tags in your post. Just highlight the codey bits and click the # on the toolbar.

I'm a little thrown off by your sheet and range reference:

Please Login or Register  to view this content.

Possibly use:

Please Login or Register  to view this content.

The whole last two years of my job has been to automate as many forms and reports as possible. If I can get it down to one or two clicks I'm happy.

[paul-bentley]

Hi daffodill11,

Thanks for the reply but unfortunately your suggestion doesn't seem to work.

Its a very strange situation as the test data set I have goes to a time 16:55 and the Macro runs and creates a chart until 15:30 and then it stops. There must be a reason for it but its beyond my meagre talents to determine why.

[daffodil11]

Is there data in Column A that far down? Your totcounter is only looking in Column A.

Another way to find the last filled cell in G would be to use the End(xlUp) method which starts at row 1.4 million and moves up until it finds an occupied row.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

A good way to test codey bits is run lines in the Immediate Window of VB summoned with Ctrl+G.
Single expressions can be tested by preceding them with a ?, such as ?1+1 or ?WorksheetFunction.Len(A1)

Simple procedures can be executed as well for testing, such as:
Sheets("Raw Data Sheet").Range("F5",Range("G" & Rows.Count).End(xlUp)).Select

[paul-bentley]

Daffodil11 you're a scholar and a gent! This has worked perfectly for me.

[daffodil11]

No problem, glad we were able to work it out.