Makro to create diagrams from a table with 89 columns

[MartinIsAsking]

Dear All

I have a table with data from a wastewater treatment plant. The table contains 89 columns. Column A is the day/date. The rest of the columns are measurements parameters from Column B till Column CH.

I would like to create a makro which create an x-y dot plot with X-axis always Column A (date) and the Y-axis the other parameters. In the end I should have 88 plots where each plot shows me the graph of one parameters vs. the date.

Unfortunately, I am not able to tell VBA to move forward by one column. I can only record the creation of one plot but then the makro will always use the same column and not move on! Can anybody help me by providing the code for such a makro? Thank you very much in advance!

 

[Andrew Poulsom]

Welcome to MrExcel.

Can't you select all the data and insert an XY(Scatter) Chart?

 

[MartinIsAsking]

I think 88 parameters in one graph might be kind of very messy Nope, I need actually 88 different plots where for each column I get a new graph. The only thing which is constant is the X-axis (date) or Column A.

 

[Andrew Poulsom]

Can you post your current macro please?

 

[MartinIsAsking]

Thanks a lot for your help....

Sub Test()
Dim lAnzahl As String
Dim i As Long
Anf:
lAnzahl = InputBox("Wie oft soll das Makro laufen ?", , 3)

If lAnzahl = "" Then Exit Sub

'Prüfen ob eine Zahl eingegeben wurde
If IsNumeric(lAnzahl) Then
For i = 1 To CLng(lAnzahl)
ActiveSheet.ChartObjects("Diagramm 4").Activate
ActiveSheet.ChartObjects("Diagramm 4").Activate
ActiveChart.ChartArea.Copy
ActiveWindow.SmallScroll Down:=12
Range("A29").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=3
ActiveSheet.ChartObjects("Diagramm 7").Activate
ActiveChart.SeriesCollection(1).Values = "='2014'!$D$2:$D$366"
ActiveChart.SeriesCollection(1).Name = "='2014'!$D$1"
ActiveChart.ChartArea.Copy
Range("K29").Select
ActiveSheet.Paste
ActiveSheet.ChartObjects("Diagramm 8").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = "='2014'!$E$2:$E$366"
ActiveChart.SeriesCollection(1).Name = "='2014'!$E$1"
ActiveSheet.ChartObjects("Diagramm 8").Activate
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
ActiveWindow.SmallScroll Down:=15
Range("A52").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=3
ActiveSheet.ChartObjects("Diagramm 9").Activate
ActiveChart.SeriesCollection(1).Values = "='2014'!$F$2:$F$366"
ActiveChart.SeriesCollection(1).Name = "='2014'!$F$1"
MsgBox "Makro Start Nr.: " & i
Next i
Else
MsgBox "Bitte ein Zahl eingeben !", vbInformation
GoTo Anf
End If

End Sub

 

[MartinIsAsking]

In any case, thanks a lot for the help. I am a full beginner when it comes to VBA programming!

 

[Andrew Poulsom]

Where is your code that sets the x axis values?

 

[MartinIsAsking]

Thanks, before recording the makro I created one diagram, which I used as template. As the X-axis stays always the same I only had to change the Y-axis. That's why it does not appear in the Makro. Of course if the makro chooses the X-axis by itself, it would be fine, but this luxury is not required. I am just all happy, if the Makro creates one diagram each time based on a new column from the table.

 

[Andrew Poulsom]

Does this work for you?

Sub Test()
    Dim LastRow As Long
    Dim LastCol As Long
    Dim r As Long
    Dim c As Long
    Dim i As Long
    With Worksheets("2014")
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
    r = 1
    c = 1
    With ActiveSheet
        For i = 2 To LastCol
            .Shapes.AddChart.Select
            With ActiveChart
                .ChartType = xlXYScatterLinesNoMarkers
                .SeriesCollection.NewSeries
                .SeriesCollection(1).Name = "='2014'!R1C" & i
                .SeriesCollection(1).XValues = "='2014'!R2C1:R" & LastRow & "C1"
                Debug.Print "='2014'!R2C" & i & ":R" & LastRow & "C" & i
                .SeriesCollection(1).Values = "='2014'!R2C" & i & ":R" & LastRow & "C" & i
            End With
            ActiveChart.Parent.Top = .Cells(r, c).Top
            ActiveChart.Parent.Left = .Cells(r, c).Left
            If c = 1 Then
                c = c + 8
            Else
                c = 1
                r = r + 15
            End If
        Next i
    End With
End Sub

 

[MartinIsAsking]

Thanks a lot,

There is a problem at the following line:

.SeriesCollection(1).Name = "='2014'!R1C" & i

Whats means with ="='2014'!R1C" & i

Shall I provide part of the excel sheet? Maybe the first 10 columns? Might be easier...