Repetitive graphing macro?

Lou38

New Member
Joined
Sep 6, 2014
Messages
8
I'm a macro newbie so I would be enormously grateful if anyone could help me solve this problem (I've tried searching various forums but not been able to adapt advice to my specific problem)

I have a worksheet with various datasets in columns which I want to plot onto a series of simple line charts. Each series represents one set of readings at 14 different frequencies, and anything from 1 to 4 readings are obtained from each subject at a particular time (as shown by the ID and date rows).

I can make the charts individually by selecting each set of columns one by one, but my dataset is very large (and increasing!) so if there's a way of doing this automatically it will save me a HUGE amount of time.

I can't seem to paste the worksheet or a screenshot of it to illustrate but can do so if helpful (and someone can explain how to! :rolleyes:)
Thanks in advance for any help..
 
Last edited:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Can't download the HTML maker to post a pic so hopefully this explains my post above a bit more (there are many more columns and although each subject has 2 sets of readings on each date in this sample, that's not always the case - sometimes there are three series per subject per date to be graphed, at other times just one) :


A
B
C
D
E
F
G
H
1
ID
DV3567
DV3567
XC0856
XC0856
DV3567
DV3567
WV2116
2
Date of reading
31/01/2014
31/01/2014
24/02/2014
24/02/2014
25/02/2014
25/02/2014
28/02/2014
3
Freq 1
12.35991
11.84177
36.24585
38.59891
39.33224
24.92511
38.19008
4
Freq 2
11.38354
10.90222
34.95918
37.63513
37.79951
21.63104
37.31819
5
Freq 3
10.46544
9.98085
32.91702
36.17519
36.09605
18.0923
36.14108
6
Freq 4
9.379013
9.01004
30.01074
33.81973
33.44479
14.11723
34.1507
7
Freq 5
8.529816
7.808243
26.5636
30.68279
29.77788
10.00668
31.34209
8
Freq 6
6.915375
6.640701
22.1763
25.95221
23.82254
6.416077
26.98416
9
Freq 7
5.542191
5.568222
17.17648
20.31355
17.13257
4.322418
21.92955
10
Freq 8
4.495209
4.71875
11.92538
14.45331
10.74013
3.452774
16.52126
11
Freq 9
3.733932
4.088715
7.28215
9.197159
5.976852
3.127533
11.30887
12
Freq 10
3.15181
3.581345
4.369141
5.514053
3.490601
2.92688
7.118454
13
Freq 11
2.699738
3.120605
3.011475
3.503738
2.472107
2.693787
4.279617
14
Freq 12
2.309723
2.676971
2.457077
2.578918
2.139893
2.416824
2.682632
15
Freq 13
1.926117
2.235077
2.117447
2.115509
1.883331
2.088486
1.953079
16
Freq 14
1.519012
1.764069
1.657608
1.597626
1.526733
1.643005
1.428406

<TBODY>
</TBODY>
 
Last edited:
Upvote 0
I don't suppose anyone can help me with this at all?
have driven myself mad trying to work it out today...
I tried the advice here http://www.mrexcel.com/forum/excel-...hs-excel-using-visual-basic-applications.html
but couldn't apply it as my graphs are formed from series in varying numbers of columns...
If I reformat my sheet so that I have one column for each test subject on a set day (with repeated readings listed consecutively in the same column, does that make it easier to manage with a macro (even though the column lengths will vary a bit)??
 
Upvote 0
Lou, there is a function called "Macro Recording" built into Excel. Can you try starting out with a data set, pressing record, building a graph all the way, and then posting the VBA? To find it, press alt+11 and select Module 1 on the left after you've recorded your macro.

I can try and modify and optimize it for you. Int he future though, this is a great way to get a baseline and learn VBA. Afterwards, I suggest checking out the changes and trying to figure out what everything does.

Regardless, happy to help if you give me a place to start.
 
Upvote 0
Thank you very much.

This is the macro recorded when I plot the first few graphs (I also repositioned the graphs so they're not on top of the data/each other, think it recorded that too!)
A3 to A16 are my x axis values for each chart, then in each column rows 3-16 contain the data I need to plot.
Rows 1 and 2 contain the information which determines how many columns are included in each scatterplot - so for these first three charts, it's just 2 columns/data series on each chart, but later on in my data set (which extends to column LJ at the moment!) there will be some graphs which include three columns/series to plot, occasionally there'll just be one column to plot. I basically want to start a new graph when the ID/date of reading change in rows 1 and 2 and it's this bit in particular that's confusing me (don't know if it will even be possible?)

Thanks again for your help (and sorry if I've rambled on!)

Sub Macro6()
'
' Macro6 Macro
'
'
Range("A1:C16").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Range("Sheet2!$A$1:$C$16")
ActiveSheet.Shapes("Chart 15").IncrementLeft -322.5
ActiveSheet.Shapes("Chart 15").IncrementTop 166.5
Range("A1:A16,D1:E16").Select
Range("D1").Activate
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Range("Sheet2!$A$1:$A$16,Sheet2!$D$1:$E$16" _
)
ActiveSheet.Shapes("Chart 16").IncrementLeft -55.5
ActiveSheet.Shapes("Chart 16").IncrementTop 203.25
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 11
ActiveSheet.Shapes("Chart 16").IncrementLeft 117.75
ActiveSheet.Shapes("Chart 16").IncrementTop -27
ActiveSheet.Shapes("Chart 16").IncrementLeft -8.25
ActiveSheet.Shapes("Chart 16").IncrementTop -9
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("A1:A16,F1:G16").Select
Range("F1").Activate
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Range("Sheet2!$A$1:$A$16,Sheet2!$F$1:$G$16" _
)
ActiveSheet.Shapes("Chart 17").IncrementLeft 432
ActiveSheet.Shapes("Chart 17").IncrementTop 168.75
ActiveWindow.SmallScroll ToRight:=-13
 
Upvote 0
Try this, let me know what you think.

Code:
Option Explicit
Sub lotsOfGraphs()
'efficiency
    Application.ScreenUpdating = False
' variables
    Dim ct                      As ChartObject
    Dim chartName               As String       ' name of chart
    Dim chartNum                As Integer      ' number of chart for name
    Dim trackLeft               As Double       ' tracks the placement of the left side of each graph
    Dim trackTop                As Double       ' tracks the placement of the top side of each graph
    Dim axisRg                  As String       ' range of data with the axis info
    Dim lastCol                 As Integer      ' last column of data
    Dim colHelper               As String       ' helper for do loop
    Dim colCounter              As Integer      ' helper for do loop
    Dim startCol                As Integer      ' tracks start col of new graph
    Dim endCol                  As Integer      ' tracks end col of new graph
    
    chartNum = 1
    trackLeft = -322.5
    trackTop = 166.5
    axisRg = "$A$1:$A$" & Cells(Rows.Count, "A").End(xlUp).Row
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    startCol = 2
    
' remove old charts
    For Each ct In ActiveSheet.ChartObjects
        ct.Delete
    Next ct
    
' add new charts
    ' loop through columns
        Do While endCol <= lastCol
            endCol = startCol
            ' loop through columns to see how many to use
                colHelper = "1"
                Do While colHelper = "1"
                    If Cells(1, endCol).Value = Cells(1, endCol + 1).Value Then
                        endCol = endCol + 1
                    Else
                        colHelper = 0
                    End If
                Loop
            ' add chart
                chartName = "Chart " & chartNum
                Range(axisRg & ",$" & ConvertToLetter(startCol) & "$1:$" & ConvertToLetter(endCol) & "$" & _
                    Cells(Rows.Count, endCol).End(xlUp).Row).Select
                ActiveSheet.Shapes.AddChart.Select
                ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
                ActiveChart.SetSourceData Source:=Range(axisRg & ",$" & ConvertToLetter(startCol) & "$1:$" & ConvertToLetter(endCol) & "$" & _
                    Cells(Rows.Count, endCol).End(xlUp).Row)
                ActiveChart.Parent.Name = chartName
                ActiveSheet.Shapes(ActiveChart.Parent.Name).IncrementLeft trackLeft
                ActiveSheet.Shapes(ActiveChart.Parent.Name).IncrementTop trackTop
            'setup next chart info
                chartNum = chartNum + 1
                trackLeft = trackLeft + ActiveSheet.Shapes(ActiveChart.Parent.Name).Width + 20
                startCol = endCol + 1
                endCol = startCol
        Loop
        
'Done!
    MsgBox "The graphs have been created successfully. The macro wil now end.", , "Success"
        
'Efficiency
    Application.ScreenUpdating = True
End Sub
Function ConvertToLetter(iCol As Integer) As String
   Dim iAlpha As Integer
   Dim iRemainder As Integer
   iAlpha = Int(iCol / 27)
   iRemainder = iCol - (iAlpha * 26)
   If iAlpha > 0 Then
      ConvertToLetter = Chr(iAlpha + 64)
   End If
   If iRemainder > 0 Then
      ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
   End If
End Function
 
Last edited:
Upvote 0
Dear Paul,

Thank you so much for your time and help.
It has partly worked :)
Graphs have plotted for the first 21 subjects who occupy columns A to AY in the worksheet (which I'm completely delighted by!)

However when I run the macro I get an error message which says:


Run-time error '1004':

Method 'Range' of object '_Global' failed


When I press the debug button, this section of the macro is highlighted in yellow:

Range(axisRg & ",$" & ConvertToLetter(startCol) & "$1:$" & ConvertToLetter(endCol) & "$" & _
Cells(Rows.Count, endCol).End(xlUp).Row).Select

I don't know if it's relevant but the 22nd subject's data is contained in columns AZ and BA - I wondered if it was something to do with changing to columns with a B prefix?
Again, thanks so much for your help- I really appreciate it!
 
Upvote 0
Indeed. The same thing happened with me. It would seem that Microsofts "ConvertToLetter" funciton has a bug! Good find, I use that a lot :)

Regardless, I have squashed it. The below code worked for me when I extended my data out.

Code:
Option Explicit
Sub lotsOfGraphs()
'efficiency
    Application.ScreenUpdating = False
' variables
    Dim ct                      As ChartObject
    Dim chartName               As String       ' name of chart
    Dim chartNum                As Integer      ' number of chart for name
    Dim trackLeft               As Double       ' tracks the placement of the left side of each graph
    Dim trackTop                As Double       ' tracks the placement of the top side of each graph
    Dim axisRg                  As String       ' range of data with the axis info
    Dim lastCol                 As Integer      ' last column of data
    Dim colHelper               As String       ' helper for do loop
    Dim colCounter              As Integer      ' helper for do loop
    Dim startCol                As Integer      ' tracks start col of new graph
    Dim endCol                  As Integer      ' tracks end col of new graph
    
    chartNum = 1
    trackLeft = -322.5
    trackTop = 166.5
    axisRg = "$A$1:$A$" & Cells(Rows.Count, "A").End(xlUp).Row
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    startCol = 2
    
' remove old charts
    For Each ct In ActiveSheet.ChartObjects
        ct.Delete
    Next ct
    
' add new charts
    ' loop through columns
        Do While endCol <= lastCol
            endCol = startCol
            ' loop through columns to see how many to use
                colHelper = "1"
                Do While colHelper = "1"
                    If Cells(1, endCol).Value = Cells(1, endCol + 1).Value Then
                        endCol = endCol + 1
                    Else
                        colHelper = 0
                    End If
                Loop
            ' add chart
                chartName = "Chart " & chartNum
                Range(axisRg & ",$" & myCTL(startCol) & "$1:$" & myCTL(endCol) & "$" & _
                    Cells(Rows.Count, endCol).End(xlUp).Row).Select
                ActiveSheet.Shapes.AddChart.Select
                ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
                ActiveChart.SetSourceData Source:=Range(axisRg & ",$" & myCTL(startCol) & "$1:$" & myCTL(endCol) & "$" & _
                    Cells(Rows.Count, endCol).End(xlUp).Row)
                ActiveChart.Parent.Name = chartName
                ActiveSheet.Shapes(ActiveChart.Parent.Name).IncrementLeft trackLeft
                ActiveSheet.Shapes(ActiveChart.Parent.Name).IncrementTop trackTop
            'setup next chart info
                chartNum = chartNum + 1
                trackLeft = trackLeft + ActiveSheet.Shapes(ActiveChart.Parent.Name).Width + 20
                startCol = endCol + 1
                endCol = startCol
        Loop
        
'Done!
    MsgBox "The graphs have been created successfully. The macro wil now end.", , "Success"
        
'Efficiency
    Application.ScreenUpdating = True
End Sub
Function myCTL(iCol As Integer)
Dim iAlpha As Integer
Dim iRemainder As Integer
   
iAlpha = Int(iCol / 27)
iRemainder = iCol - (iAlpha * 26)
If iAlpha > 0 Then
   myCTL = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
    If iRemainder >= 27 Then iRemainder = iRemainder - 1
    myCTL = myCTL & Chr(iRemainder + 64)
End If
End Function
 
Upvote 0
Wait! That's wrong! Now the bug has been squashed

Code:
Option Explicit Sub lotsOfGraphs() 'efficiency     Application.ScreenUpdating = False ' variables     Dim ct                      As ChartObject     Dim chartName               As String       ' name of chart     Dim chartNum                As Integer      ' number of chart for name     Dim trackLeft               As Double       ' tracks the placement of the left side of each graph     Dim trackTop                As Double       ' tracks the placement of the top side of each graph     Dim axisRg                  As String       ' range of data with the axis info     Dim lastCol                 As Integer      ' last column of data     Dim colHelper               As String       ' helper for do loop     Dim colCounter              As Integer      ' helper for do loop     Dim startCol                As Integer      ' tracks start col of new graph     Dim endCol                  As Integer      ' tracks end col of new graph          chartNum = 1     trackLeft = -322.5     trackTop = 166.5     axisRg = "$A$1:$A$" & Cells(Rows.Count, "A").End(xlUp).Row     lastCol = Cells(1, Columns.Count).End(xlToLeft).Column     startCol = 2      ' remove old charts     For Each ct In ActiveSheet.ChartObjects         ct.Delete     Next ct      ' add new charts     ' loop through columns         Do While endCol <= lastCol             endCol = startCol             ' loop through columns to see how many to use                 colHelper = "1"                 Do While colHelper = "1"                     If Cells(1, endCol).Value = Cells(1, endCol + 1).Value Then                         endCol = endCol + 1                     Else                         colHelper = 0                     End If                 Loop             ' add chart                 chartName = "Chart " & chartNum                 Range(axisRg & ",$" & ConvertToLetter(startCol) & "$1:$" & ConvertToLetter(endCol) & "$" & _                     Cells(Rows.Count, endCol).End(xlUp).Row).Select                 ActiveSheet.Shapes.AddChart.Select                 ActiveChart.ChartType = xlXYScatterSmoothNoMarkers                 ActiveChart.SetSourceData Source:=Range(axisRg & ",$" & ConvertToLetter(startCol) & "$1:$" & ConvertToLetter(endCol) & "$" & _                     Cells(Rows.Count, endCol).End(xlUp).Row)                 ActiveChart.Parent.Name = chartName                 ActiveSheet.Shapes(ActiveChart.Parent.Name).IncrementLeft trackLeft                 ActiveSheet.Shapes(ActiveChart.Parent.Name).IncrementTop trackTop             'setup next chart info                 chartNum = chartNum + 1                 trackLeft = trackLeft + ActiveSheet.Shapes(ActiveChart.Parent.Name).Width + 20                 startCol = endCol + 1                 endCol = startCol         Loop          'Done!     MsgBox "The graphs have been created successfully. The macro wil now end.", , "Success"          'Efficiency     Application.ScreenUpdating = True End Sub Function ConvertToLetter(iCol As Integer) As String    Dim iAlpha As Integer    Dim iRemainder As Integer    iAlpha = Int(iCol / 26)    iRemainder = iCol - (iAlpha * 26)    If iAlpha > 0 Then       ConvertToLetter = Chr(iAlpha + 64)    End If    If iRemainder > 0 Then       ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)    End If End Function</pre>
 
Upvote 0
That looks like crap. Sorry the formatting is bad. Just use the original code and change the below line where the red is. It will be 27. change it to 26

Code:
iAlpha = Int(iCol/27)

to
Code:
iAlpha = Int(iCol/[COLOR=#ff0000]26[/COLOR])
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top