This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hi,
I am using the below piece of code to add a trendline to a graph. But it fails with runtime error 13 - type mismatch
Sub AddTrendline()
Dim GraphName As String
Dim TType As String
GraphName = Sheets("Control").Cells(11, 2).Value
TType = "xl" & Sheets("Control").Cells(9, 2).Value ' Tredline Type - Contains Linear
PO = Sheets("Control").Cells(10, 2).Value ' Trendline Parameter
Call UPS(Sheets("Control").Cells(11, 2).Value, "6785")
Sheets(GraphName).Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=TType).Select
Call PS(Sheets("Control").Cells(11, 2).Value, "6785")
End Sub
The varialble TType is instantiated correctly.
If I change the code to the below it works. I put the trendline type explicitly.
ActiveChart.SeriesCollection(1).Trendlines.Add(Type**:="xlLinear").**Select
Sub AddTrendline()
Dim GraphName As String
Dim TType As String
GraphName = Sheets("Control").Cells(11, 2).Value
TType = "xl" & Sheets("Control").Cells(9, 2).Value ' Tredline Type - Contains Linear
PO = Sheets("Control").Cells(10, 2).Value ' Trendline Parameter
Call UPS(Sheets("Control").Cells(11, 2).Value, "6785")
Sheets(GraphName).Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Type:="xlLinear").Select
Call PS(Sheets("Control").Cells(11, 2).Value, "6785")
End Sub
How can I pass in a variable to set the trendline. The same would apply if I user Power or Polynomial types as there us a second parameter.
Reagrds
Tim
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
If PO is your variable for polynomial order, you can do something like this:
If TType = xlPolynomial Then
PO = Sheets("Control").Cells(10, 2).Value
Else
PO = Null
End If
Hans,
Thank you that worked a dream. I will now work on setting the other options based on Trendline type, for example . if the Trendline Type is xlPolynomial, I want to set the trendline order so will need a second parameter to be set, Variable type from doc must be Variant, but contain and an integer from 2 to 6 inclusive. Kind regards,Tim
The Trendline Type is an integer, not a text string.
If you specify the type as a literal string "xlLinear", Excel is smart enough to translate it to the corresponding integer, but that doesn't work if you use a string variable.
Try this:
Sub AddTrendline()
Dim GraphName As String
Dim TType As XlTrendlineType
'Dim PO
GraphName = Sheets("Control").Cells(11, 2).Value
Select Case Sheets("Control").Cells(9, 2).Value
Case "Linear"
TType = xlLinear
Case "Polynomial"
TType = xlPolynomial
Case "Exponential"
TType = xlExponential
Case "Logarithmic"
TType = xlLogarithmic
Case "Power"
TType = xlPower
Case "MovingAvg"
TType = xlMovingAvg
End Select
' PO doesn't get used...
'PO = Sheets("Control").Cells(10, 2).Value ' Trendline Parameter
Call UPS(GraphName, "6785")
Sheets(GraphName).Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=TType).Select
Call PS(GraphName, "6785")
End Sub