Dynamic chart

Grek

Board Regular
Joined
May 9, 2004
Messages
90
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.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Grek

Board Regular
Joined
May 9, 2004
Messages
90
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#
 

Grek

Board Regular
Joined
May 9, 2004
Messages
90
Hi, I tried the code below found on the internet 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
 

Grek

Board Regular
Joined
May 9, 2004
Messages
90
Thanks for the link, but I would like to find a solution in VBA if possible...
Nobody can help me ?
 

Forum statistics

Threads
1,141,587
Messages
5,707,252
Members
421,498
Latest member
matinebi

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
Top