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]

It's a formula in R1C1 reference style. If i is 2 it would be ='2014'!B1 in A1 reference style. The code assumes that you have headings in row 1.

 

[MartinIsAsking]

Yep, the headings are in row 1. Values start from row2.

Stilll don't understand the code:

.SeriesCollection(1).Name = "='2014'!R1C" & i Is there missing a 1 then? you said R1C1 but there is only R1C.....I assume & i means that it goes on each time by one column?

I get one graph, before there comes an error announcement. This one graph contains several parameters instead of several graphs with one parameter . There is also no Date on the X-Axis (Column A). But it looks already quite good

 

[Andrew Poulsom]

I tested the code before posting it. Can you post some sample data please?

If i is 2 the formula would be ='2014'!R1C2 in R1C1 reference style.

 

[MartinIsAsking]

Here is the link to the sample data. Thx a lot.

 

[MartinIsAsking]

Wait, it does not work...

https://www.dropbox.com/sh/mm3drlugky7qbn9/AADW07PN9SRFmKyc3FtwPpufa?dl=0

Copy paste shoud be fine!

 

[Andrew Poulsom]

There isn't any VBA code in that workbook but this works for me:

Sub Test()
    Dim LastRow As Long
    Dim LastCol As Long
    Dim r As Long
    Dim c As Long
    Dim i As Long
    With Worksheets("2012-2014")
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
    r = 1
    c = 1
    Worksheets.Add
    With ActiveSheet
        For i = 2 To LastCol
            .Shapes.AddChart.Select
            With ActiveChart
                .ChartType = xlXYScatterLinesNoMarkers
                .SeriesCollection.NewSeries
                .SeriesCollection(1).Name = "='2012-2014'!R1C" & i
                .SeriesCollection(1).XValues = "='2012-2014'!R2C1:R" & LastRow & "C1"
                .SeriesCollection(1).Values = "='2012-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.

According to the debugger there is a problem at the same line as before (Z20):

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

Am I doing something wrong? I have a german version of Excel 2007? Could this be the reason?

I tried to write:

.SeriesCollection(1).Name = "='2012-2014'!R1C1" & i

but this did not help.

I executed the macro step by step and that's where I got stuck.

Anyway, I should send you some swiss chocolate for all the help

Cheers

 

[Andrew Poulsom]

I think its Z1S1 in Germany so adjust to suit. You can check the format by entering a formula in a cell and switching to R1C1 notation.

 

[MartinIsAsking]

Changing the above mentioned line with the following line worked for me:

.SeriesCollection(1).Name = "='2012-2014'!" & Cells(1, i).Address


Don't ask me why. Got help I think it is due to the Excel version I have: German and vs. 2007

Thank you very much. The final solution is:

Sub Test()
Dim LastRow As Long
Dim LastCol As Long
Dim r As Long
Dim c As Long
Dim i As Long
With Worksheets("2012-2014")
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
r = 1
c = 1
Worksheets.Add
With ActiveSheet
For i = 2 To LastCol
.Shapes.AddChart.Select
With ActiveChart
.ChartType = xlXYScatter
.SeriesCollection.NewSeries
.SeriesCollection(1).Name = "='2012-2014'!" & Cells(1, i).Address
.SeriesCollection(1).XValues = "='2012-2014'!R2C1:R" & LastRow & "C1"
.SeriesCollection(1).Values = "='2012-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

 

[Andrew Poulsom]

Interesting that R1C1 works for XValues and Values but not for Name.