Thread: Dynamic Chart

Hello,

I wish to create a dynamic chart (basic line chart) using excel and VBA.
However , the chart should always display the last 15 lines of column A (date), C (value X) and E (value Y).
Can someone tell me how to do that in VBA ?

Many thanks in advance,

Greg.

Actually I tried what is below but the macro bugs on this part :

ActiveChart.SetSourceData Source:=Sheets("Asia Asia").Range( _
varDateChart, varNavChart, varIndexChart), PlotBy:=xlColumns

here is the code :

Sheets("Asia Asia(CHART)").Select

Sheets("Asia Asia").Select

Range("A4").Select
Range(Selection, Selection.End(xlDown)).Select
varCount = Selection.Count

varDateChart = Range("A" & varCount - 15 & ":A" & varCount + 3).Select
varNavChart = Range("C" & varCount - 15 & ":A" & varCount + 3).Select
varIndexChart = Range("E" & varCount - 15 & ":A" & varCount + 3).Select



Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("Asia Asia").Range( _
varDateChart, varNavChart, varIndexChart), PlotBy:=xlColumns
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.HasDataTable = True
ActiveChart.DataTable.ShowLegendKey = True
ActiveSheet.Shapes("Chart 1").IncrementLeft 576#
ActiveSheet.Shapes("Chart 1").IncrementTop -132#
ActiveSheet.Shapes("Chart 1").IncrementLeft 96#

Thanks for the link !

I tried the code below but this part doesn't work :

Set myDataRange = Range("A" & varCount - 15 & ":A" & varCount + 3, "C" & varCount - 15 & ":C" & varCount + 3, "E" & varCount - 15 & ":E" & varCount + 3)

Actually it only works with A and C but when I add E it doesn't work anymore....

code :

Range("A4").Select
Range(Selection, Selection.End(xlDown)).Select
varCount = Selection.Count

varDateChart = Range("A" & varCount - 15 & ":A" & varCount + 3).Select
varNavChart = Range("C" & varCount - 15 & ":A" & varCount + 3).Select
varIndexChart = Range("E" & varCount - 15 & ":A" & varCount + 3).Select


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 = Range("A" & varCount - 15 & ":A" & varCount + 3, "C" & varCount - 15 & ":C" & varCount + 3, "E" & varCount - 15 & ":E" & varCount + 3)
' 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

Nobody can help me ?