1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48
| Option Explicit
Sub TraceGraphe()
Dim Ws As Worksheet
Dim PlageX As Range
Dim Ch As Chart
Application.ScreenUpdating = False
Set Ws = ThisWorkbook.Worksheets("Feuil1")
Set PlageX = Ws.Range(Ws.Cells(DEB(Ws), 1), Ws.Cells(FIN(Ws), 1))
Set Ch = Ws.ChartObjects.Add(150, 50, 400, 240).Chart
With Ch.SeriesCollection.NewSeries
.ChartType = xlXYScatter
.XValues = PlageX
.Values = PlageX.Offset(, 1)
.Trendlines.Add
.Trendlines(1).DisplayEquation = True
End With
Set Ch = Nothing
Set PlageX = Nothing
Set Ws = Nothing
End Sub
Private Function DEB(ByVal Ws As Worksheet) As Long
Dim ShName As String
Dim LastLig As Long
With Ws
ShName = "'" & Ws.Name & "'!"
LastLig = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
DEB = Evaluate("=MATCH(VLOOKUP(0.00499999," & ShName & "A2:A" & LastLig & ",1)," & ShName & "A1:A" & LastLig & ",0)+1")
End Function
Private Function FIN(ByVal Ws As Worksheet) As Long
Dim ShName As String
Dim LastLig As Long
With Ws
ShName = "'" & Ws.Name & "'!"
LastLig = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
FIN = Evaluate("=MATCH(VLOOKUP(0.2999999," & ShName & "A2:A" & LastLig & ",1)," & ShName & "A1:A" & LastLig & ",0)")
End Function |
Partager