This code works in Excel 2003, but gives me a Type Mismatch error in Excel
2007.

Sub aaa()

Dim submissionXValsRange
Set submissionXValsRange = Sheets("Sheet1").Range("A1:A5")

Dim submissionYValsRange
Set submissionYValsRange = Sheets("Sheet1").Range("B1:B5")

' Actual submission values scatter chart
Dim MyNewSrs As Series
Set MyNewSrs = Sheets("Chart1").SeriesCollection.NewSeries
With MyNewSrs
.Name = "Data"
.Values = submissionYValsRange
.XValues = submissionXValsRange
.Trendlines.Add Type:=xlLinear, Name:="Linear Trendline ",
Forward:=2000, DisplayEquation:=True
End With

End Sub