Plotting Line Graph in a loop throug columns

[RosaTol]

Hey guys,

I have never ever programmed with vba. But i need to make 70 line plots from every individual column in a data set, so i figured i'd give it a try. I found a code that does what i want for a certain column. This is the code when i do it for my first column (B), in column A there is the x-values i want to plot every column with.

Sub Test()
Dim LastRow As Long
Dim Rng1 As Range
Dim ShName As String
With ActiveSheet
LastRow = .Range("B" & .Rows.Count).End(xlUp).Row
Set Rng1 = .Range("B2:B" & LastRow) ' & ", E2:E" & LastRow)
ShName = .Name
End With
Charts.Add

With ActiveChart
.ChartType = xlLine
.HasTitle = True
.ChartTitle.Text = "='Testdata'!$B$1"
.SetSourceData Source:=Rng1
.Location Where:=xlLocationAsObject, Name:=ShName
End With

With ActiveChart.SeriesCollection(1)
.Name = "='Testdata'!$B$1"
End With
End Sub


What i wanted to do next was make a loop that executes this code for every column. I found two things:

'Sub colnaam()
'colName = Split(Range.Offset(0, i).Address, "$")(1)
' MsgBox colName
'End Sub


'Function myColName(colNum As Long) As String
' myColName = Left(Range(0, colNum).Address(False, False), _
' 1 - (colNum > 10))
'End Function

The top sub gives the error: argument not optional, and higlights "Range", i dont know what this means.
But because i dont know what it means it is hard for me to implement. Seeing that i have to hand in my final report next monday im kinda stressing, and therefore have no time to try and learn the language. I really hope someone can help me with this!

 

[Worf]

Hello and welcome to the Board

Try this:

Sub Test()
Dim LastRow&, Rng1 As Range, ShName$, cn$, i%, co As ChartObject

For i = 2 To 4      ' loop the columns
    cn = Split(Cells(1, i).Address, "$")(1)
    With ActiveSheet
        LastRow = .Range(cn & .Rows.Count).End(xlUp).Row
        Set Rng1 = .Range(cn & "2:" & cn & LastRow)
        ShName = .Name
    End With
    Set co = ActiveSheet.ChartObjects.Add(Left:=Cells(4, i * 6).Left, _
    Width:=Range("a1:f1").Width, Top:=Cells(3 * i, 3).Top, Height:=Range("a1:a9").Height)
    With co.Chart
        .ChartType = xlLine
        .HasTitle = True
        .ChartTitle.Text = "='Testdata'!$" & cn & "$1"
        .SetSourceData Source:=Rng1
        .Location Where:=xlLocationAsObject, Name:=ShName
        .SeriesCollection(1).Name = "='Testdata'!$" & cn & "$1"
    End With
Next
End Sub

 

[RosaTol]

Thank you very much! That helps a lot. The only problem now is that as a title it gives: #!REF. What does this mean? I have the titles in the first row of every column. That is wat the line .SeriesCollection(1).Name = "='Testdata'!$" & cn & "$1" does right? Getting the names out of the first row?

 

[Worf]

You will get #REF! as a title if there is no sheet named Testdata.