Hello

I have a chart that I am building programmatically depending on whether the
user has selected an option from a range of cells. There are 5 columns, each
column has a header (which is populated with a value slected from an incell
drop down box). The header cells have the word '* select *' in them until a
value is chosen.
If the user only populates 2 of the columns then the chart will only build
using those 2 columns.

Code below for reference.

I have declared and set 5 'value' ranges for the values

My chart is working fine until I try to add the values to the
SeriesCollection, which gives a type mismatch error. Presume this is because
I'm trying to mix a Range and a Variable (i)

Can you tell me what I'm doing wrong, and more to the point, how to fix it?

Thanks

Trevor

Sub UpdateStratSegsII()

If Worksheets("Model").Range("E9") = "" Then
MsgBox ("No data available")
Sheets("Model").Select
Exit Sub
End If

Dim rng, cell As Range
Dim myXValues, myValues1, myValues2, myValues3, myValues4, myValues5 As Range

Set rng = Sheets("Model").Range("J8:N8")

Set myChart = Sheets("Strategic Segments").ChartObjects("Chart 1").Chart

'set x axis values

With Worksheets("Model")
If .Range("e10") = "" Then
Set myXValues = .Range("E9")
Else: Set myXValues = .Range(.Range("E9"), .Range("E9").End(xlDown))
End If

'set seg1values:

If .Range("j10") = "" Then
Set myValues1 = .Range("J9")
Else: Set myValues1 = .Range(.Range("j9"),
..Range("j9").End(xlDown))
End If

'set seg2values

If .Range("K10") = "" Then
Set myValues2 = .Range("K9")
Else: Set myValues2 = .Range(.Range("k9"),
..Range("k9").End(xlDown))
End If

'set seg3values

If .Range("L10") = "" Then
Set myValues3 = .Range("L9")
Else: Set myValues3 = .Range(.Range("l9"),
..Range("l9").End(xlDown))
End If

'set seg4values

If .Range("M10") = "" Then
Set myValues3 = .Range("M9")
Else: Set myValues4 = .Range(.Range("m9"),
..Range("m9").End(xlDown))
End If

'set seg4values

If .Range("N10") = "" Then
Set myValues5 = .Range("M9")
Else: Set myValues5 = .Range(.Range("N9"),
..Range("N9").End(xlDown))
End If

End With

'remove existing series
For Each mySeries In myChart.SeriesCollection
mySeries.Delete
Next mySeries

'add new series
i = 0

For Each cell In rng
If cell "* Select *" Then
With myChart
.SeriesCollection.NewSeries
.SeriesCollection(i + 1).Name = cell
.SeriesCollection(i + 1).XValues = myXValues
.SeriesCollection(i + 1).Values = myValues(i) '**THIS IS WHERE
IT GOES WRONG**
End With
End If

i = i + 1

Next cell

End Sub