ich bin neu hier und absolut kein VBA-Experte. Ich habe mir einen kurzen Code zusammengeschustert, um eine Trendliniengleichung auszulesen und damit Berechnungen anzustellen. Das funktioniert ganz gut unter Excel 2010, unter Excel 2013 hingegen nicht mehr. Bitte auf seichtem Niveau erklären.
Herzlichen Dank
Private Sub CommandButton1_Click()
Dim trendtext As String
Dim T As Variant
Dim s As Variant
Dim p As Variant
Dim q As Variant
Dim c As Variant
Dim k As Variant
Dim j As Variant
Dim i As Variant
T = Range("I3").Value
p = Range("G3").Value
q = Range("G4").Value
c = Clear
c = Range("M1").Value
ActiveSheet.ChartObjects(1).Activate
ActiveChart.SeriesCollection(1).Select
If T = 1 Then
s = True
Else
If T 1 Then
s = False
End If
End If
If Range("G3").Value 1 Then
p = Range("G3").Value
ActiveChart.SeriesCollection(c).Trendlines.Add(Type:=xlPolynomial, Order:=p, Forward:=q, _
Backward:=0, DisplayEquation:=True, DisplayRSquared:=s).Select
With Selection.Format.Line
.Visible = msoTrue
.DashStyle = msoLineSolid
End With
With Selection.Format.Line
.Visible = msoTrue
.Weight = 0.75
End With
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(192, 0, 0)
.Transparency = 0
End With
With Selection.DataLabel
.NumberFormat = "#.##0,00"
End With
ActiveSheet.ChartObjects(1).Activate
ActiveChart.SeriesCollection(1).Trendlines(1).Select
With Selection
.DisplayEquation = True
.DisplayRSquared = False
trendtext = .DataLabel.Text
End With
Range("M3").Value = trendtext
trendtext = Replace(trendtext, "x", "x^")
trendtext = Replace(trendtext, "x^ ", "x ")
trendtext = Replace(trendtext, " + ", "+")
trendtext = Replace(trendtext, " - ", "-")
trendtext = Replace(trendtext, "x", "*K3")
trendtext = Replace(trendtext, " = ", "=")
trendtext = Replace(trendtext, "y", "")
Range("M3").FormulaLocal = trendtext
ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
Selection.NumberFormat = "#.##0,00"
Selection.Left = 144
Selection.Top = 12