Modify VBA code for different data structure

[artz]

Hi,

I've found some VBA code that could do what I need if I could figure out how to modify it. After a number of attempts at trying to modify it to do what I need, I am posting it to the Forum.

Basically, the code takes several several rows and columns of data and creates a series of charts on a separate worksheet, Charts.

One obvious difference is that the x-values called in the code are in a row compared to a column as in my data.

Also, in the code, fixed lengths of the data fields are used. My data varies in the number of rows and columns of data. In the original worksheet, the X-values are in a row: E6 to BE6. My data has the x-data in starting column M, at M3. Y-values begin in column N, at N3.

Below is an excerpt from the original worksheet data:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	*	*	*	*	*	*	*	*	*	*	*
2	*	*	*	*	*	*	*	*	*	*	*
3	*	*	*	*	*	*	*	*	*	*	*
4	*	*	*	*	20100107	20100114	20100121	20100128	20100204	20100211	20100218
5	*	*	*	*	*	*	*	*	*	*	*
6	*	*	*	*	7-Jan-10	14-Jan-10	21-Jan-10	28-Jan-10	4-Feb-10	11-Feb-10	18-Feb-10
7	*	29.20	Task 1	ACWP	1.0	2.0	3.0	4.0	5.0	6.0	7.0
8	*	29.20	Task 1	BCWP	2.0	3.0	4.0	5.0	6.0	7.0	8.0
9	*	29.20	Task 1	BCWS	3.0	4.0	5.0	6.0	7.0	8.0	9.0
10	*	29.20	Task 1	EAC	4.0	5.0	6.0	7.0	8.0	9.0	10.0
11	*	29.21	Task 2	ACWP	5.0	6.0	7.0	8.0	9.0	10.0	11.0
12	*	29.21	Task 2	BCWP	6.0	7.0	8.0	9.0	10.0	11.0	12.0
13	*	29.21	Task 2	BCWS	7.0	8.0	9.0	10.0	11.0	12.0	13.0
14	*	29.21	Task 2	EAC	8.0	9.0	10.0	11.0	12.0	13.0	14.0
15	*	29.22	Task 3	ACWP	9.0	10.0	11.0	12.0	13.0	14.0	15.0
16	*	29.22	Task 3	BCWP	10.0	11.0	12.0	13.0	14.0	15.0	16.0
17	*	29.22	Task 3	BCWS	11.0	12.0	13.0	14.0	15.0	16.0	17.0
18	*	29.22	Task 3	EAC	12.0	13.0	14.0	15.0	16.0	17.0	18.0
19	*	29.23	Task 4	ACWP	13.0	14.0	15.0	16.0	17.0	18.0	19.0
20	*	29.23	Task 4	BCWP	14.0	15.0	16.0	17.0	18.0	19.0	20.0
21	*	29.23	Task 4	BCWS	15.0	16.0	17.0	18.0	19.0	20.0	21.0
22	*	29.23	Task 4	EAC	16.0	17.0	18.0	19.0	20.0	21.0	22.0
23	*	29.24	Task 5	ACWP	17.0	18.0	19.0	20.0	21.0	22.0	23.0
Data

Again, below is the sub which creates the charts on the Charts worksheet:

Sub Refresh()

'Variables
'wsCharts and wsData are the two worksheets
' conSpacing is the constant for the spacing between graphs (20 rows)
' this is based on how excel creates graphs on my machine, it changes if
' excel isn't full screen (I think)
' i and j are just counters
' LastRow is the final data entry in the table in column C

    Dim wsCharts As Worksheet
    Set wsCharts = Worksheets("Charts")
    Dim wsData As Worksheet
    Set wsData = Worksheets("Data")
    Const conSpacing = 20, conStartInRow = 6
    Dim i, j, LastRow As Long
    
'Delete all existing charts on the page

    wsCharts.ChartObjects.Delete

'Find the last data entry in column C

    With wsData
        LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    End With
    
'Add a blank chart
'Linemarkers is the chart type
'set a dummy data range, and tell the graph to plot by rows
'Put the chard in the charts worksheet
'Tell the sheet it has a title
'Set the x and y axis as Date and Hours
'Cut the sample chart

    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
        ActiveChart.SetSourceData Source:=wsData.Range("E6:BE10"), PlotBy:= _
        xlRows
    ActiveChart.Location where:=xlLocationAsObject, Name:=wsCharts.Name
    With ActiveChart
        .HasTitle = True
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Hours"
    End With
    wsCharts.ChartObjects.Cut
    
'For 1 to the number of groups of four

    For i = 1 To (LastRow - conStartInRow) / 4
    
'If it is the first i, select cell A1, else select the cell according to the spacing
        
        If i = 1 Then
            wsCharts.Cells(1, 1).Select
        Else
            wsCharts.Cells((i - 1) * conSpacing, 1).Select
        End If
        
'Paste the chart to this location

        wsCharts.Paste
        
'Set the series 1 to 4, according to the 4 data ranges in the table
        
        For j = 0 To 3
    
            ActiveChart.SeriesCollection(j + 1).XValues = "=" & wsData.Name & "!R6C5:R6C57"
            
            ActiveChart.SeriesCollection(j + 1).Values = "=" & wsData.Name & "!R" & _
                                (i * 4) + (conStartInRow / 2) + j & "C5:" _
                                & "R" & (i * 4) + (conStartInRow / 2) + j & "C57"
        
            ActiveChart.SeriesCollection(j + 1).Name = "=" & wsData.Name & "!R" & _
                                    (i * 4) + (conStartInRow / 2) + j & "C3:" _
                                    & "R" & (i * 4) + (conStartInRow / 2) + j & "C4"
            
        Next

'Give the chart a title of the first label of the data series

        With ActiveChart
            .ChartTitle.Characters.Text = wsData.Cells(i * 4 + conStartInRow, 3)
        End With
    Next
    
    

End Sub

My data is laid out a little bit differently. As mentioned above, x-values are in column M with y-values starting in column N extending to column BE.

Also the data length in the original VBA code is fixed. My number of data rows is variable.

Another difference too, is that the original VBA creates multiple series on the same chart. I need one set of x-values (column M) and one y-series (starting at column N) for each chart.

A sample of my worksheet is shown below:

Excel Workbook
	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	*	*	*	*	*	*	*	*	*	*	*	*	*
2	Date	AA	AIG	AXP	BA	C	CAT	DD	DIS	GE	GM	HD	HON
3	1/3/12	9.2	24.07	48.21	73.79	28.32	93.56	46.14	38.31	18.2	21.05	42.14	55.23
4	1/4/12	9.42	23.92	48.24	73.9	28.16	94.43	46.64	38.85	18.4	21.15	42.74	55.18
5	1/5/12	9.33	23.91	48.8	73.1	28.5	95.09	46.33	39.5	18.39	22.17	43.09	55.24
6	1/6/12	9.13	23.54	48.27	73.55	28.54	95.33	45.67	39.91	18.49	22.92	43.2	54.83
7	1/9/12	9.4	24	48.39	74.09	29.07	96.67	46.06	39.75	18.69	22.84	43.23	55.29
8	1/10/12	9.41	25.07	48.67	74.56	29.99	99.52	46.76	39.63	18.56	23.24	43.53	56.23
9	1/11/12	9.6	25.37	48.95	74.3	31.26	99.2	46.92	38.7	18.71	24.47	43.46	56.11
10	1/12/12	9.9	25.43	49.65	75.07	31.59	101.49	47.71	38.73	18.76	24.67	43.39	56.83
11	1/13/12	9.77	24.95	49.76	74.16	30.73	102.02	48.01	38.4	18.67	24.29	43.51	56.34
12	1/17/12	9.73	24.56	50.22	74.8	28.21	102.91	48.15	38.48	18.58	24.2	43.74	56.8
13	1/18/12	9.99	25.23	50.56	74.62	29.02	104.26	49.05	39.02	18.85	24.51	44.88	57.83
14	1/19/12	10.15	25.55	50.95	75.12	29.32	105.75	49	39.44	18.98	24.82	45.41	58.13
15	1/20/12	10.14	25.65	50.04	75.08	29.63	105.64	49.02	39.31	18.98	25	44.51	57.02
16	1/23/12	10.22	25.44	49.44	75.07	29.84	106.37	48.95	39.25	18.77	24.92	44.88	57.26
17	1/24/12	10.24	25.32	49.23	74.92	29.89	106.29	49.01	39.25	18.67	24.79	44.96	57.27
18	1/25/12	10.45	25.31	50.17	75.38	29.95	109.05	50.18	39.56	18.96	24.92	45.26	57.75
19	1/26/12	10.33	25.14	49.98	74.87	30.37	111.31	50.53	39.35	18.9	24.72	44.95	57.47
20	1/27/12	10.4	25.25	49.85	74.11	30.86	111.28	50.31	39.25	18.86	24.37	44.87	57.9
21	1/30/12	10.29	25.2	49.12	73.73	30.22	110.41	50.56	38.99	18.73	24.23	44.77	57.82
22	1/31/12	10.13	25.11	50.14	73.75	30.71	109.12	50.48	38.9	18.55	24.02	44.39	57.68
Data

Could any of the VBA experts in the Forum take a stab at modifying the code above to create the series of charts based the VBA code above but using my data format?

Any help is greatly appreciated.

Thanks,

Art