VBA Code to automatically graph varying amounts of data

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!
philmcfearlessAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RobSampsonCommented:
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
philmcfearlessAuthor Commented:
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
RobSampsonCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

RobSampsonCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
philmcfearlessAuthor Commented:
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
RobSampsonCommented:
Hi there, any luck with this one?

Regards,

Rob.
0
RobSampsonCommented:
Thanks for the grade.

Regards,

Rob.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.