Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VBA Code to automatically graph varying amounts of data

Posted on 2008-11-03
9
Medium Priority
?
929 Views
Last Modified: 2013-12-20
Hi,
Im trying to develop VBA code to automatically graph a varying amount of data in excel,
I have a list of numbers, after a cell with the word "system" which needs to be graphed.

The cell with "system" occurs a number of times and each occurrence would be a different series on the same graph.
Hopefully that introduces the problem sufficiently!

So what I need the macro to do is search the excel sheet for the word "system"  then select all the numerical data below that cell until it hits an empty cell and then work its way through the end of the sheet for all the remaining occurrences of "system",
All while graphing the series of numbers!
0
Comment
Question by:philmcfearless
  • 5
  • 2
7 Comments
 
LVL 65

Assisted Solution

by:RobSampson
RobSampson earned 2000 total points
ID: 22881575
Hi, code like that below would create a graph for a specific range.  If you could code the identification of the source range, the below code could be modified to suit.

Maybe if you could post a sample data file, we could code the range in for you.

Regards,

Rob.
Set objExcel = CreateObject("Excel.Application")
Set objChart = Nothing
Const xlDelimited = 1
Const xlDoubleQuote = 1
Const xlUp = -4162
Const xlLineMarkers = 65
Const xlLocationAsObject = 2
Const xlRows = 1
objExcel.Visible = True
 
strSourceFile = "C:\Temp\MyData.xls"
 
Set objDataWB = objExcel.Workbooks.Open(strSourceFile)
Set objDataSheet = objDataWB.Sheets(1)
Set objChartSheet = objDataWB.Sheets(2)
intLastRow = objDataSheet.Cells(65536, "A").End(xlUp).Row
objDataSheet.Range("A1:P" & intLastRow).Copy objChartSheet.Range("A1")
If objChart Is Nothing Then
	Set objChart = objChartWB.Charts.Add
	'ActiveSheet.ChartObjects("Chart 1").Activate
	objChart.ChartType = xlLineMarkers
	objChart.SetSourceData objChartSheet.Range("A1:P" & intLastRow)
	objChart.Location xlLocationAsObject, objChartSheet.Name
Else
	objChartSheet.ChartObjects("Chart 1").Activate
	objExcel.ActiveChart.SetSourceData objChartSheet.Range("A1:P" & intLastRow), xlRows
End If

Open in new window

0
 

Author Comment

by:philmcfearless
ID: 22971134
Thanks for the reply!
Iv attached a rough sample of what im trying to do
In the file there are 3 "line numbers", but this will vary from file to file.
Also there are 3 "System Numbers", but this will also vary!
Each graph is for x, y or z values for each of the line numbers so the number of graphs is varying too!
Lastly each series on the graph is a System number.

So what I would like to do is search through the excel sheet for each occurrence of "line number" and "system line", maybe have a loop running until all is graphed
Any help is appreciated


 
Sampledata.xls
0
 
LVL 65

Assisted Solution

by:RobSampson
RobSampson earned 2000 total points
ID: 23125354
Hi, very sorry for my delay.....I've been away for a while.  I just quickly recorded the macro for you, and here's the code that will generate the graph for Series X in your sample data.  I will work on making it more dynamic....

Regards,

Rob.
Sub Create_Chart()
    Range("A1").Select
    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1"), PlotBy:= _
        xlColumns
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Values = "=Sheet1!R3C2:R16C2"
    ActiveChart.SeriesCollection(1).Name = "=""Sys No 1"""
    ActiveChart.SeriesCollection(2).Values = "=Sheet1!R20C2:R33C2"
    ActiveChart.SeriesCollection(2).Name = "=""Sys No 12"""
    ActiveChart.SeriesCollection(3).Values = "=Sheet1!R37C2:R42C2"
    ActiveChart.SeriesCollection(3).Name = "=""Sys No 45"""
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    Range("A1").Select
End Sub

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 65

Accepted Solution

by:
RobSampson earned 2000 total points
ID: 23125646
OK, try this code out....

Regards,

Rob.
Sub Create_Chart()
    strColNumber = UCase(InputBox("Which column letter do you want to graph (not the axis label)?", "Column Letter", "B"))
    If IsNumeric(strColNumber) Then
        MsgBox "You need to enter a column letter."
        Exit Sub
    End If
    
    strColNumber = CStr(Asc(strColNumber) - 64)
 
    strActiveSheetName = ActiveSheet.Name
    
    ' Get a count of how times "System No" appears in column A
    strSeriesRanges = ""
    For intRow = 1 To Cells(65536, "A").End(xlUp).Row
        If Cells(intRow, "A").Value = "System No" Then
            If strSeriesRanges = "" Then
                strSeriesRanges = "=""Sys No " & Cells(intRow + 1, "A").Value & """;=" & strActiveSheetName & "!R" & CStr(intRow + 1) & "C" & strColNumber
            Else
                strSeriesRanges = strSeriesRanges & ":R" & intRow - 3 & "C" & strColNumber & vbCrLf & "=""Sys No " & Cells(intRow + 1, "A").Value & """;=" & strActiveSheetName & "!R" & CStr(intRow + 1) & "C" & strColNumber
            End If
        End If
    Next
    strSeriesRanges = strSeriesRanges & ":" & "R" & Cells(65536, "A").End(xlUp).Row - 1 & "C" & strColNumber
    
    arrSeries = Split(strSeriesRanges, vbCrLf)
    
    ' Create the Chart object
    Range("A1").Select
    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1"), PlotBy:=xlColumns
    
    ' Create the Series ranges
    ActiveChart.SeriesCollection(1).Delete
    For intSeries = 1 To UBound(arrSeries) + 1
        strValues = Split(arrSeries(intSeries - 1), ";")(1)
        strName = Split(arrSeries(intSeries - 1), ";")(0)
        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.SeriesCollection(intSeries).Values = strValues
        ActiveChart.SeriesCollection(intSeries).Name = strName
    Next
    
    ActiveChart.Location Where:=xlLocationAsObject, Name:=strActiveSheetName
 
    Range("A1").Select
End Sub

Open in new window

0
 

Author Comment

by:philmcfearless
ID: 23178317
Hi Thanks for the code, have been busy so will try this out, looks good seems to work, will get back to you if I have any problems
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 23345841
Hi there, any luck with this one?

Regards,

Rob.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 23350010
Thanks for the grade.

Regards,

Rob.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question