Help Creating Code
 

I'm very much a beginner when it comes to programming, so hope to
appeal those experts amongst you for help.

I have a spreadsheet (Sheet1) which contains data in rows (row 1 has
column titles whilst 2 to 189 contain names in col1, along with
numbers in col 2 & 3).

Name Amount 1 Amount 2
Average 5 10
Smith 5 10
Jones 7 11
Wilson 6 9

What I need to produce is a graph for each individual which shows
their achievement against the average. Ie Graph 1 should show Series 1
= Amount 1 for Average and Smith and Series 2 = Amount 2 for Average
and Smith. Graph 2 (separate page) should show Series 1 = Amount 1 for
Average and Jones and Series 2 = Amount 2 for Average and Jones. etc
all the way through to Name 189.

I can do the formatting of the graph, but I don't know how to 'tell'
it where/how to get the data from. I haven't even begun to get to
grips with arrays and stuff yet, so any help at all would be
gratefully received.

Thanks in advance.

Help Creating Code
 

Chart data in Excel must be in contiguous cells. Therefore, you will
need to put the data for each chart in its own set of contiguous cell.
The following code does that and then creates the chart for each name
you have in your data.


Sub MakeCharts()
Dim ws As Worksheet
Dim iRow as Integer
Dim aName as String

Set ws = Sheets("Sheet1")
On Error Resume Next
'avoid prompt when deleting chart
Application.DisplayAlerts = False
ws.Columns("E:G").ClearContents
iRow = 3
Do
'create chart data in Cols E-G
ws.Cells(4 * iRow - 11, 5) = ws.Cells(1, 1)
ws.Cells(4 * iRow - 11, 6) = ws.Cells(1, 2)
ws.Cells(4 * iRow - 11, 7) = ws.Cells(1, 3)
ws.Cells(4 * iRow - 10, 5) = ws.Cells(2, 1)
ws.Cells(4 * iRow - 10, 6) = ws.Cells(2, 2)
ws.Cells(4 * iRow - 10, 7) = ws.Cells(2, 3)
ws.Cells(4 * iRow - 9, 5) = ws.Cells(iRow, 1)
ws.Cells(4 * iRow - 9, 6) = ws.Cells(iRow, 2)
ws.Cells(4 * iRow - 9, 7) = ws.Cells(iRow, 3)

'grab name for new chart and title
aName = ws.Cells(iRow, 1)
'delete old chart
Sheets(aName).Delete
'make new chart
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData _
Source:=ws.Range(ws.Cells(4 * iRow - 11, 5), _
ws.Cells(4 * iRow - 9, 7)), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsNewSheet, _
Name:=aName
With ActiveChart
.Name = aName
.HasTitle = True
.ChartTitle.Characters.Text = aName
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
iRow = iRow + 1
Loop Until ws.Cells(iRow, 1) = ""
Application.DisplayAlerts = True

End Sub

Hth,
Merjet

Help Creating Code
 

Have you tried looking at pivot tables and charts, I don't know alot about
them but I think it does what your after.

"mate" wrote:

I'm very much a beginner when it comes to programming, so hope to
appeal those experts amongst you for help.

I have a spreadsheet (Sheet1) which contains data in rows (row 1 has
column titles whilst 2 to 189 contain names in col1, along with
numbers in col 2 & 3).

Name Amount 1 Amount 2
Average 5 10
Smith 5 10
Jones 7 11
Wilson 6 9

What I need to produce is a graph for each individual which shows
their achievement against the average. Ie Graph 1 should show Series 1
= Amount 1 for Average and Smith and Series 2 = Amount 2 for Average
and Smith. Graph 2 (separate page) should show Series 1 = Amount 1 for
Average and Jones and Series 2 = Amount 2 for Average and Jones. etc
all the way through to Name 189.

I can do the formatting of the graph, but I don't know how to 'tell'
it where/how to get the data from. I haven't even begun to get to
grips with arrays and stuff yet, so any help at all would be
gratefully received.

Thanks in advance.

Help Creating Code
 

Apologies for not replying sooner, but thanks very much for this code
- it was exactly what I needed.

Thanks again.


On 14 Mar, 01:06, "merjet" wrote:
Chart data in Excel must be in contiguous cells. Therefore, you will
need to put the data for each chart in its own set of contiguous cell.
The followingcodedoes that and then creates the chart for each name
you have in your data.

Sub MakeCharts()
Dim ws As Worksheet
Dim iRow as Integer
Dim aName as String

Set ws = Sheets("Sheet1")
On Error Resume Next
'avoid prompt when deleting chart
Application.DisplayAlerts = False
ws.Columns("E:G").ClearContents
iRow = 3
Do
'create chart data in Cols E-G
ws.Cells(4 * iRow - 11, 5) = ws.Cells(1, 1)
ws.Cells(4 * iRow - 11, 6) = ws.Cells(1, 2)
ws.Cells(4 * iRow - 11, 7) = ws.Cells(1, 3)
ws.Cells(4 * iRow - 10, 5) = ws.Cells(2, 1)
ws.Cells(4 * iRow - 10, 6) = ws.Cells(2, 2)
ws.Cells(4 * iRow - 10, 7) = ws.Cells(2, 3)
ws.Cells(4 * iRow - 9, 5) = ws.Cells(iRow, 1)
ws.Cells(4 * iRow - 9, 6) = ws.Cells(iRow, 2)
ws.Cells(4 * iRow - 9, 7) = ws.Cells(iRow, 3)

'grab name for new chart and title
aName = ws.Cells(iRow, 1)
'delete old chart
Sheets(aName).Delete
'make new chart
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData _
Source:=ws.Range(ws.Cells(4 * iRow - 11, 5), _
ws.Cells(4 * iRow - 9, 7)), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsNewSheet, _
Name:=aName
With ActiveChart
.Name = aName
.HasTitle = True
.ChartTitle.Characters.Text = aName
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
iRow = iRow + 1
Loop Until ws.Cells(iRow, 1) = ""
Application.DisplayAlerts = True

End Sub

Hth,
Merjet