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
