Type Mismatch problem...

[Trevor Williams]

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

 

[Tom Ogilvy]

You trying to use myvalues as an array, but didn't set it up as an array. so
this sets it up as an array

also, you need to declare your variables like this:

Dim rng as Range, cell As Range

not like this
Dim rng, cell As Range

Sub UpdateStratSegsII()

Dim myValues(1 to 5) as Range
If Worksheets("Model").Range("E9") = "" Then
MsgBox ("No data available")
Sheets("Model").Select
Exit Sub
End If

Dim rng as Range, cell 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:

Dim j as Long

for j = 10 to 15
If .cells(9,j) = "" Then
Set myValues(j - 9) = .cells(9,j)
Else: Set myValues(j - 9) = .Range(.cells(9,j), _
.cells(9,j).End(xlDown))
End If
Next

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)
End With
End If

i = i + 1

Next cell

End Sub

 

[Trevor Williams]

Thanks Tom. You've made it a whole lot neater (and smaller)!

I'm getting a run time error 9 - subscript out of range whilst the macros'
setting up myValues

Set myValues(j - 9) = .Cells(9, j)

Any ideas?

Trevor

 

[Trevor Williams]

Hi Tom

I've sussed what was wrong - the loop should have been 10 to 14 (only 5
columns) so it's all working splendidly now.

Thanks again.

Trevor