I'll try to be very precise with my problem but if you have any questions, please ask!
So basicelly I have a column A full of dates (from line 1 to line 80) and a column B full of values (stock prices, and also with the same range). I have created a chart with this code :
Sub Chart()
Range(Cells(1, 1), Cells(80, 1))Select
Range(Selection, Selection.Offset(0, 1)).Select
Set rSource=selection
ActiveChart.SetSourceData Source:=rSource, PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
end sub
So this is not a chart object but a new chart on a new sheet (I say that because all the things I've read on this subject is dealing with chart objects and not with chart alone in a sheet). Now what I need is to draw an horizontal line between two dates (a support line). For example, I need to draw a line between 01/01/2013 (line 1) and 06/25/2013 (line 80). The line has to stay at the same level on the y-axis (that's what we call an horizontal line :) ) at the level of the stock price of 01/01/2013.
I've tried that :
Sub supportline()
Dim CoordA As Variant
CoordA = [a1:b80]
ActiveChart.Shapes.AddLine(CoordA(1, 1), CoordA(1, 2), CoordA(80, 1), CoordA(80, 2)).Select
end sub
But the line isn't drawn where I want. I think there's a problem of scale but I can't figure out how to have the accurate coordinates.
Thank you very much for your time and your help
EDIT (06/11/2013) I've simplified the data base to illustrate my problem : http://cjoint.com/?CFksHluf0VD I'm trying to make a line between the two dates (01/01/2013 and 16/01/2013)
If you download this file, you'll see in the VBA part : 1/ Test1 : I've tried to make a line to link 2 points of the chart : if you launch the sub you'll see that the line is drawn but at the wrong scale and wrong place 2/ Test 2: I've tried to make an horizontal line between two points but that doesn't work at all
Test 2 is what I need to do for my original project (an horizontal line which goes from one date to another with the same y-value)