How to specify values range in Excel chart?

[sjh]

Hi, I have a question about creating graphs to Excel sheets programatically. In my app, when user clicks a button, a report(summary of numbers) is generated, and I would like to add a graph below the report.

I don't know the right syntax for dynamically specifying .XValues, .Values, and .Name. I tried the following, but it didn't work.
ActiveChart.SeriesCollection(1).XValues = "=Sheets("Summary Report&quot.Range(Cells(iRow,10), Cells(iRow,iCount))"


Please help! Thank you,
____________________________________________
iCount = 10
iRow = 1
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=wBudgetCatRpt.Range("A16&quot
ActiveChart.SeriesCollection.NewSeries

ActiveChart.SeriesCollection(1).XValues = "=wBudgetCatRpt.Range(Cells(iRow,10), Cells(iRow,iCount))"

ActiveChart.SeriesCollection(1).Values = "='Summary Report'!R10C2:R13C2"
ActiveChart.SeriesCollection(1).Name = "='Summary Report'!R9C2"

''comment: add more series here

ActiveChart.Location Where:=xlLocationAsObject, Name:="Summary Report"

 

[Chattin]

Bit of a brain fart this seeing as its Sunday afternoon - have a pick through these macros - for info the last one is recorded and then slightly tweaked so you might be able to do something similar

HTH

http://www.geocities.com/jonpeltier/Excel/ChartsHowTo/QuickChartVBA.html

Sub ChartObjectAdd()
'Excel uses Charts.Add followed later by ActiveChart.Location to create a chart object _
and uses ActiveChart.SetSourceData to set all the series data in one shot. _
The coding is efficient in terms of the small length of the code _
but inefficient in terms of giving you some control over the output. _
In my examples I use ChartObjects.Add, which also requires (or allows) me to state the position and size of the chart. _
'This example does exactly what the recorded macro above does:

With ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=375, Top:=75, Height:=225)
.Chart.ChartType = xlXYScatterLines
.Chart.SetSourceData Source:=Sheets("Sheet1&quot.Range("A3:G14&quot
End With
End Sub

'You may find it more convenient, in a longer procedure, to define some object variables.
'The next procedure does the same as the two above, but it uses a ChartObject variable for the new chart object that we create. If we need to refer to this chart object later in the procedure, we can conveniently use the variable myChtObj.

Sub ChartMake()
Dim myChtObj As ChartObject
'
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=375, Top:=75, Height:=225) 'You can anchor a chart to a _
cell by setting the Top and Left properties of the ChartObject to equal the Top and Left properties of the cell.
myChtObj.Chart.ChartType = xlXYScatterLines
myChtObj.Chart.SetSourceData Source:=Sheets("Sheet1&quot.Range("A3:G14&quot
'You are not limited to using SetSourceData to define the data being charted.
'You can add the series one-by-one, selecting the precise data you want, not what Excel will assume you want.
End Sub

Sub ChartGenerate()

Worksheets("Sheet1&quot.Activate ' Activate the worksheet.

Range("C4&quot.Select ' The upper left corner of the chart
' data.

' Select the current range of data. This line of code assumes
' that the current region of cells is contiguous, without empty
' rows or columns.
Selection.CurrentRegion.Select

Selection.Name = "Firstname" ' Define a name to the first range.

' Repeat same steps for next contiguous range of cells.
Range("C12&quot.Select
Selection.CurrentRegion.Select
Selection.Name = "Secondname"

Range("Firstname, Secondname&quot.Select ' Select both ranges of
' cells.
myRange = Selection.Address
mysheetname = ActiveSheet.Name

'Add a chart to the active sheet
ActiveSheet.ChartObjects.Add(125.25, 60, 301.5, 155.25).Select

' Or, to create a chart on a separate chart sheet, substitute the
' next line for one above.

' Charts.Add

Application.CutCopyMode = False

' This line can best be written by recording a macro, and
' modifying the code generated by Microsoft Excel.

ActiveChart.ChartWizard _
Source:=Sheets(mysheetname).Range(myRange), _
Gallery:=xlLine, Format:=4, PlotBy:=xlColumns, _
CategoryLabels:=1, SeriesLabels:=1, HasLegend:=1, _
Title:="", CategoryTitle:="", ValueTitle:="", ExtraTitle:=""

End Sub
Sub ChartProduce()
' Select the cell in the upper-left corner of the chart.
Range("C3&quot.Select
' Select the current range of data. This line of code assumes that
' the current region of cells is contiguous - without empty rows
' or columns.
Selection.CurrentRegion.Select
' Assign the address of the selected range of cells to a variable.
myRange = Selection.Address
' Assign the name of the active sheet to a variable. This line is
' used in order to allow a chart to be created on a separate chart
' sheet.
mysheetname = ActiveSheet.Name
' Add a chart to the active sheet.
ActiveSheet.ChartObjects.Add(125.25, 60, 301.5, 155.25).Select
' To create a chart on a separate chart sheet, remark out the
' previous line, and substitute the next line for the one above.
' Charts.Add
Application.CutCopyMode = False
' This line can best be written by recording a macro, and
' modifying the code generated by the Microsoft Excel Macro
' recorder.
ActiveChart.ChartWizard _
Source:=Sheets(mysheetname).Range(myRange), _
Gallery:=xlLine, Format:=4, PlotBy:=xlRows, _
CategoryLabels:=1, SeriesLabels:=1, HasLegend:=1, _
Title:="", CategoryTitle:="", _
ValueTitle:="", ExtraTitle:=""
End Sub

Sub DynamicChart()
'The macro uses the CurrentRegion property to find data adjacent to cell C3 on Sheet1 _
and create a column chart on a new chart sheet.

'Create a New Chart
Charts.Add
'Change the Chart Type to the type that you want
ActiveChart.ChartType = xlColumnClustered
'Sets the Source Data to the Region of data starting with the
'top left corner C3. This line of code assumes that the
'region of cells is contiguous - without empty rows or columns.
ActiveChart.SetSourceData _
Source:=Sheets("Sheet1&quot.Range("C3&quot.CurrentRegion, _
PlotBy:=xlColumns
End Sub

Sub ChartInteractiveCreate()
'From Jon Peltier
Dim objChart As ChartObject
Dim myChtRange As Range
Dim myDataRange As Range
With ActiveSheet
' What range should chart cover
Set myChtRange = Application.InputBox( _
Prompt:="Select a range where the chart should appear.", _
Title:="Select Chart Position", Type:=8)
' What range contains data for chart
Set myDataRange = Application.InputBox( _
Prompt:="Select a range containing the chart data.", _
Title:="Select Chart Data", Type:=8)
' Cover chart range with chart
Set objChart = .ChartObjects.Add( _
Left:=myChtRange.Left, Top:=myChtRange.Top, _
Width:=myChtRange.Width, Height:=myChtRange.Height)
' Put all the right stuff in the chart
With objChart.Chart
.ChartArea.AutoScaleFont = False
.ChartType = xlXYScatterLines
.SetSourceData Source:=myDataRange
.HasTitle = True
.ChartTitle.Characters.Text = "My Title"
.ChartTitle.Font.Bold = True
.ChartTitle.Font.Size = 12
With .Axes(xlCategory, xlPrimary)
.HasTitle = True
With .AxisTitle
.Characters.Text = "My X Axis"
.Font.Size = 10
.Font.Bold = True
End With
End With
With .Axes(xlValue, xlPrimary)
.HasTitle = True
With .AxisTitle
.Characters.Text = "My Y Axis"
.Font.Size = 10
.Font.Bold = True
End With
End With
End With
End With
End Sub

Sub ChartOnTheFly()

'Prevents screen refresh whilst macro executes
Application.ScreenUpdating = False

Application.StatusBar = "Please wait ... creating charts"

Application.Worksheets("Data Entry Sheet&quot.Activate
Dim rngall As Range
Set rngall = Application.Names("all_data&quot.RefersToRange

Dim chtall As ChartObject
Set chtall = ActiveSheet.ChartObjects.Add _
(Left:=5, Width:=1000, Top:=1490, Height:=750)
With chtall
.Chart.ChartType = xlXYScatter
.Chart.SetSourceData rngall, xlColumns
.Chart.Location Where:=xlLocationAsObject, Name:="Data Entry Sheet"
.Chart.HasTitle = True
.Chart.ChartTitle.Text = "='Data Entry Sheet'!R46C38"
.Chart.Axes(xlCategory, xlPrimary).HasTitle = True
.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Actuation no."
.Chart.Axes(xlValue, xlPrimary).HasTitle = True
.Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Concentration (µg/act)"
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.PlotArea.Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
With Selection.Interior
.ColorIndex = 15
.PatternColorIndex = 1
.Pattern = xlSolid
End With
With Selection.Border
.Weight = xlHairline
.LineStyle = xlNone
End With
Selection.Interior.ColorIndex = xlNone
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
ActiveChart.Legend.Select
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.ColorIndex = 1
.Weight = xlHairline
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.SeriesCollection(4).Select
With Selection.Border
.ColorIndex = 41
.Weight = xlHairline
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.ColorIndex = 41
.Weight = xlHairline
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.SeriesCollection(6).Select
With Selection.Border
.ColorIndex = 5
.Weight = xlHairline
.LineStyle = xlDashDot
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.SeriesCollection(5).Select
With Selection.Border
.ColorIndex = 5
.Weight = xlHairline
.LineStyle = xlDashDot
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.SeriesCollection(8).Select
With Selection.Border
.ColorIndex = 57
.Weight = xlHairline
.LineStyle = xlDash
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.SeriesCollection(7).Select
With Selection.Border
.ColorIndex = 5
.Weight = xlHairline
.LineStyle = xlDash
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = ActiveSheet.Cells(104, 2)
.MinorUnitIsAuto = True
.MajorUnit = 30
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = False
End With
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 2.5
.MaximumScale = 6.5
.MinorUnit = 0.5
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
.TickLabels.NumberFormat = "0.00"
End With

With ActiveChart.PageSetup
.LeftHeader = "Dose Content Uniformity"
.RightHeader = "Page __ of __ "
.LeftFooter = "Analyst:" & Chr(10) & "" & Chr(10) & "Checker:"
.CenterFooter = "Date:" & Chr(10) & "" & Chr(10) & "Date:"
.RightFooter = "Printed at &T on &D"
End With

Application.StatusBar = False

End Sub