Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Type Mismatch problem...
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 181
Default Type Mismatch problem...
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Type Mismatch problem...
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

--
Regards,
Tom Ogilvy


"Trevor Williams" wrote:

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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 181
Default Type Mismatch problem...
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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Trevor Williams" wrote:

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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 181
Default Type Mismatch problem...
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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Trevor Williams" wrote:

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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Type mismatch problem Dan Excel Programming 7 May 31st 06 04:55 PM
Type mismatch problem in array - ?? ina Excel Programming 6 May 17th 06 12:56 PM
Type mismatch error problem when dealing with Strings David Goodall[_2_] Excel Programming 4 June 30th 05 01:40 PM
Type Mismatch Problem Damien McBain[_2_] Excel Programming 2 May 20th 05 04:09 PM
Type mismatch problem? NooK[_45_] Excel Programming 3 August 4th 04 01:07 PM


All times are GMT +1. The time now is 10:32 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement