Chart Variables

Can anyone help me!??

I'm trying to create a chart using VBA. There are countless possible options and formats to this chart.

What I have done is set up a table that will tell Excel how to format each series that is added to a chart. It is a simple column table with information such as linestyle, color index, markerstyle etc...

The code works fine apart for setting the linestyle and markerstyle. I keep getting an error with this area of the code. I have set a string variable for each and do a vlookup against the table to get me the relevant linestyle/markerstyle (eg.xlContinuous). I know the Vlookup works fine.

However, excel will not change the chart accordingly it says that it is 'unable to set the Linestyle property of the Border Class'.

I have attached the code below

Does anyone know how I can get round this problem?

Many thanks

Chris

Sub CreateChart()

Dim LStyle As String
Dim MStyle As String
Dim CI As Integer
Dim MBC As Integer
Dim MFC As Integer
Dim i As Integer

ChartClearAll

'On Error Resume Next

For i = 1 To CR - 1
Sheets("Interactive Chart").Activate

With ActiveChart.SeriesCollection.NewSeries

.Name = WorksheetFunction.VLookup(CellReference(i), Sheets("Master Table").Range("b2:t4033"), 6, False)

.Values = Sheets("WCI Data Sheet Run Rate").Range(CellReference(i), Range(CellReference(i)).Offset(0, Range("control").Value))

.XValues = Sheets("WCI Data Sheet Run Rate").Range("B3", Sheets("WCI Data Sheet Run Rate").Range("B3").Offset(0, Sheets("WCI Data Sheet Run Rate").Range("control").Value))

End With

ActiveChart.SeriesCollection(WorksheetFunction.VLookup(CellReference(i), Sheets("Master Table").Range("b2:t4033"), 6, False).Select

CI = WorksheetFunction.VLookup(CellReference(i), Sheets("Master Table").Range("b2:t4033"), 10, False)

LStyle = WorksheetFunction.VLookup(CellReference(i), Sheets("Master Table").Range("b2:t4033"), 8, False)

MBC = WorksheetFunction.VLookup(CellReference(i), Sheets("Master Table").Range("b2:t4033"), 11, False)

MFC = WorksheetFunction.VLookup(CellReference(i), Sheets("Master Table").Range("b2:t4033"), 12, False)

MStyle = WorksheetFunction.VLookup(CellReference(i), Sheets("Master Table").Range("b2:t4033"), 9, False)

With Selection.Border
.ColorIndex = CI
.Weight = xlThin
.LineStyle = LStyle
End With

With Selection
.MarkerBackgroundColorIndex = MBC
.MarkerForegroundColorIndex = MFC
.MarkerStyle = MStyle
End With
Next i

End Sub