ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   run time error 13 type mismatch (https://www.excelbanter.com/excel-programming/304527-re-run-time-error-13-type-mismatch.html)

Rob Bovey
run time error 13 type mismatch
 
Hi Jodi,

The only circumstances that I know of that would cause this error in the
code you've shown below is if one of the cells that you are looping through
contains an error value. Try placing a check for an error value prior to
testing for the character like so:

Dim Inrange As Range
Dim rng As Range
Set Inrange = Range("B16,B58,T4,T15,T23,T50")
For Each rng In Inrange.Cells
If Not IsError(rng.Value) Then
If rng.Value = "c" Then
rng.Font.Name = "Wingdings 3"
Else
rng.Font.Name = "Webdings"
End If
End If
Next rng

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Jodi" wrote in message
...
VBA Specialists,

I am receiving a run time error 13 type mismatch, with this line
highlighted:
If rng.Value = "c" Then

I have a workbook with 27 worksheets. 23 of the 27 are identical and use
the following code:
Private Sub Worksheet_Calculate()

Dim Inrange As Range
Dim rng As Range
Set Inrange = Range("B16,B58,T4,T15,T23,T50")
For Each rng In Inrange.Cells
If rng.Value = "c" Then
rng.Font.Name = "Wingdings 3"
Else
rng.Font.Name = "Webdings"
End If
Next rng

End Sub

4 of the 27 are slightly different and have a different range
Range("B16,B58,T4,T20,T34,T50")
In these 4 worksheets, cells T15 and T23 contain text strings. I know this
is causing the problem, but I want to know is there a way to easily solve
this?

The worksheets still function as designed, but a message box continuously
pops up that must be clicked and is quite annoying. Thanks for any
assistance.

Jodi

Jodi
run time error 13 type mismatch
 
Thanks Rob.

I believe the iserror worked. I appreciate you and kkknie helping me.

Jodi

"Rob Bovey" wrote:

Hi Jodi,

The only circumstances that I know of that would cause this error in the
code you've shown below is if one of the cells that you are looping through
contains an error value. Try placing a check for an error value prior to
testing for the character like so:

Dim Inrange As Range
Dim rng As Range
Set Inrange = Range("B16,B58,T4,T15,T23,T50")
For Each rng In Inrange.Cells
If Not IsError(rng.Value) Then
If rng.Value = "c" Then
rng.Font.Name = "Wingdings 3"
Else
rng.Font.Name = "Webdings"
End If
End If
Next rng

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Jodi" wrote in message
...
VBA Specialists,

I am receiving a run time error 13 type mismatch, with this line
highlighted:
If rng.Value = "c" Then

I have a workbook with 27 worksheets. 23 of the 27 are identical and use
the following code:
Private Sub Worksheet_Calculate()

Dim Inrange As Range
Dim rng As Range
Set Inrange = Range("B16,B58,T4,T15,T23,T50")
For Each rng In Inrange.Cells
If rng.Value = "c" Then
rng.Font.Name = "Wingdings 3"
Else
rng.Font.Name = "Webdings"
End If
Next rng

End Sub

4 of the 27 are slightly different and have a different range
Range("B16,B58,T4,T20,T34,T50")
In these 4 worksheets, cells T15 and T23 contain text strings. I know this
is causing the problem, but I want to know is there a way to easily solve
this?

The worksheets still function as designed, but a message box continuously
pops up that must be clicked and is quite annoying. Thanks for any
assistance.

Jodi

dg
run time error 13 type mismatch
 
Hi,

I have a similar problem when trying to set a range to be used for plotting
graph. Below is my code. this is very urgent. Please help!!!

Sub updateGraphSheet(inputSheet)

Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range
Dim rng6 As Range
Dim rng7 As Range
Dim rng8 As Range
Dim rng9 As Range
Dim rng10 As Range
Dim rng11 As Range
Dim rng12 As Range
Dim rng13 As Range
Dim rng14 As Range
Dim rng15 As Range
Dim rng16 As Range
Dim rng17 As Range
Dim rng18 As Range
Dim rng19 As Range
Dim rng20 As Range
Dim rng21 As Range
Dim rng22 As Range
Dim rng23 As Range
Dim rng24 As Range
Dim rng25 As Range
Dim rng26 As Range
Dim rng27 As Range
Dim rng28 As Range
Dim rng29 As Range
Dim rng30 As Range
Dim rng31 As Range
Dim rng32 As Range
Dim rng33 As Range
Dim rng34 As Range
Dim rng35 As Range
Dim rng36 As Range
Dim rng37 As Range
Dim rng38 As Range
Dim rng39 As Range
Dim rng40 As Range
Dim rng41 As Range
Dim iRow2 As Integer
Dim iRow1 As Integer

'iRow2 = Worksheets(inputSheet).UsedRange.Rows.Count
'MsgBox (iRow2)
iRow2 = Sheets(inputSheet).Range("d65536").End(xlUp).Row
'iRow2 = LastRow(Sheets(inputSheet))
'iRow2 = 34
iRow1 = 4

Set rng1 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "a"),
Sheets(inputSheet).Cells(iRow2, "a"))

Set rng2 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "b"),
Sheets(inputSheet).Cells(iRow2, "b"))

Set rng3 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "et"),
Sheets(inputSheet).Cells(iRow2, "et"))
Set rng4 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "eu"),
Sheets(inputSheet).Cells(iRow2, "eu"))
Set rng5 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "ev"),
Sheets(inputSheet).Cells(iRow2, "ev"))
Set rng6 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "ew"),
Sheets(inputSheet).Cells(iRow2, "ew"))
Set rng7 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "ex"),
Sheets(inputSheet).Cells(iRow2, "ex"))

Set rng8 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "ey"),
Sheets(inputSheet).Cells(iRow2, "ey"))
Set rng9 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "ez"),
Sheets(inputSheet).Cells(iRow2, "ez"))
Set rng10 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "fa"),
Sheets(inputSheet).Cells(iRow2, "fa"))
Set rng11 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "fb"),
Sheets(inputSheet).Cells(iRow2, "fb"))
Set rng12 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "fc"),
Sheets(inputSheet).Cells(iRow2, "fc"))

Set rng13 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "fd"),
Sheets(inputSheet).Cells(iRow2, "fd"))
Set rng14 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "fe"),
Sheets(inputSheet).Cells(iRow2, "fe"))
Set rng15 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "ff"),
Sheets(inputSheet).Cells(iRow2, "ff"))
Set rng16 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "fg"),
Sheets(inputSheet).Cells(iRow2, "fg"))
Set rng17 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "fh"),
Sheets(inputSheet).Cells(iRow2, "fh"))

Set rng18 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "fi"),
Sheets(inputSheet).Cells(iRow2, "fi"))
Set rng19 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "fj"),
Sheets(inputSheet).Cells(iRow2, "fj"))
Set rng20 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "fk"),
Sheets(inputSheet).Cells(iRow2, "fk"))
Set rng21 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "fl"),
Sheets(inputSheet).Cells(iRow2, "fl"))
Set rng22 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "fm"),
Sheets(inputSheet).Cells(iRow2, "fm"))
Set rng23 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "fn"),
Sheets(inputSheet).Cells(iRow2, "fn"))

Set rng24 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "fo"),
Sheets(inputSheet).Cells(iRow2, "fo"))
Set rng25 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "fp"),
Sheets(inputSheet).Cells(iRow2, "fp"))
Set rng26 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "fq"),
Sheets(inputSheet).Cells(iRow2, "fq"))
Set rng27 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "fr"),
Sheets(inputSheet).Cells(iRow2, "fr"))
Set rng28 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "fs"),
Sheets(inputSheet).Cells(iRow2, "fs"))

Set rng29 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "ft"),
Sheets(inputSheet).Cells(iRow2, "ft"))
Set rng30 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "fu"),
Sheets(inputSheet).Cells(iRow2, "fu"))
Set rng31 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "fv"),
Sheets(inputSheet).Cells(iRow2, "fv"))

Set rng32 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "fw"),
Sheets(inputSheet).Cells(iRow2, "fw"))
Set rng33 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "fx"),
Sheets(inputSheet).Cells(iRow2, "fx"))

Set rng34 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "gb"),
Sheets(inputSheet).Cells(iRow2, "gb"))
Set rng35 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "gc"),
Sheets(inputSheet).Cells(iRow2, "gc"))
Set rng36 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "gd"),
Sheets(inputSheet).Cells(iRow2, "gd"))
Set rng37 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "ge"),
Sheets(inputSheet).Cells(iRow2, "ge"))
Set rng38 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "gf"),
Sheets(inputSheet).Cells(iRow2, "gf"))

Set rng39 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "gg"),
Sheets(inputSheet).Cells(iRow2, "gg"))

Set rng40 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "gh"),
Sheets(inputSheet).Cells(iRow2, "gh"))
Set rng41 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "gi"),
Sheets(inputSheet).Cells(iRow2, "gi"))

Worksheets(2).ChartObjects("Set1_FAP").Activate
ActiveChart.SeriesCollection(1).XValues = rng1
ActiveChart.SeriesCollection(1).Values = rng2

Worksheets(2).ChartObjects("Set1_SRB").Activate
ActiveChart.SeriesCollection(1).XValues = rng1
ActiveChart.SeriesCollection(1).Values = rng3
ActiveChart.SeriesCollection(2).XValues = rng1
ActiveChart.SeriesCollection(2).Values = rng4
ActiveChart.SeriesCollection(3).XValues = rng1
ActiveChart.SeriesCollection(3).Values = rng5
ActiveChart.SeriesCollection(4).XValues = rng1
ActiveChart.SeriesCollection(4).Values = rng6
ActiveChart.SeriesCollection(5).XValues = rng1
ActiveChart.SeriesCollection(5).Values = rng7

Worksheets(2).ChartObjects("Set1_Voice").Activate
ActiveChart.SeriesCollection(1).XValues = rng1
ActiveChart.SeriesCollection(1).Values = rng8
ActiveChart.SeriesCollection(2).XValues = rng1
ActiveChart.SeriesCollection(2).Values = rng9
ActiveChart.SeriesCollection(3).XValues = rng1
ActiveChart.SeriesCollection(3).Values = rng10
ActiveChart.SeriesCollection(4).XValues = rng1
ActiveChart.SeriesCollection(4).Values = rng11
ActiveChart.SeriesCollection(5).XValues = rng1
ActiveChart.SeriesCollection(5).Values = rng12

Worksheets(2).ChartObjects("Set1_VIDEO").Activate
ActiveChart.SeriesCollection(1).XValues = rng1
ActiveChart.SeriesCollection(1).Values = rng13
ActiveChart.SeriesCollection(2).XValues = rng1
ActiveChart.SeriesCollection(2).Values = rng14
ActiveChart.SeriesCollection(3).XValues = rng1
ActiveChart.SeriesCollection(3).Values = rng15
ActiveChart.SeriesCollection(4).XValues = rng1
ActiveChart.SeriesCollection(4).Values = rng16
ActiveChart.SeriesCollection(5).XValues = rng1
ActiveChart.SeriesCollection(5).Values = rng17

Worksheets(2).ChartObjects("Set1_Data").Activate
ActiveChart.SeriesCollection(1).XValues = rng1
ActiveChart.SeriesCollection(1).Values = rng18
ActiveChart.SeriesCollection(2).XValues = rng1
ActiveChart.SeriesCollection(2).Values = rng19
ActiveChart.SeriesCollection(3).XValues = rng1
ActiveChart.SeriesCollection(3).Values = rng20
ActiveChart.SeriesCollection(4).XValues = rng1
ActiveChart.SeriesCollection(4).Values = rng21
ActiveChart.SeriesCollection(5).XValues = rng1
ActiveChart.SeriesCollection(5).Values = rng22
ActiveChart.SeriesCollection(6).XValues = rng1
ActiveChart.SeriesCollection(6).Values = rng23

Worksheets(2).ChartObjects("Set1_HSDPA").Activate
ActiveChart.SeriesCollection(1).XValues = rng1
ActiveChart.SeriesCollection(1).Values = rng24
ActiveChart.SeriesCollection(2).XValues = rng1
ActiveChart.SeriesCollection(2).Values = rng25
ActiveChart.SeriesCollection(3).XValues = rng1
ActiveChart.SeriesCollection(3).Values = rng26
ActiveChart.SeriesCollection(4).XValues = rng1
ActiveChart.SeriesCollection(4).Values = rng27
ActiveChart.SeriesCollection(5).XValues = rng1
ActiveChart.SeriesCollection(5).Values = rng28

Worksheets(2).ChartObjects("Set1_RRC").Activate
ActiveChart.SeriesCollection(1).XValues = rng1
ActiveChart.SeriesCollection(1).Values = rng29
ActiveChart.SeriesCollection(2).XValues = rng1
ActiveChart.SeriesCollection(2).Values = rng30
ActiveChart.SeriesCollection(3).XValues = rng1
ActiveChart.SeriesCollection(3).Values = rng31

Worksheets(2).ChartObjects("Set1_User").Activate
ActiveChart.SeriesCollection(1).XValues = rng1
ActiveChart.SeriesCollection(1).Values = rng32
ActiveChart.SeriesCollection(2).XValues = rng1
ActiveChart.SeriesCollection(2).Values = rng33

Worksheets(2).ChartObjects("Set1_Handout").Activat e
ActiveChart.SeriesCollection(1).XValues = rng1
ActiveChart.SeriesCollection(1).Values = rng34
ActiveChart.SeriesCollection(2).XValues = rng1
ActiveChart.SeriesCollection(2).Values = rng35
ActiveChart.SeriesCollection(3).XValues = rng1
ActiveChart.SeriesCollection(3).Values = rng36
ActiveChart.SeriesCollection(4).XValues = rng1
ActiveChart.SeriesCollection(4).Values = rng37
ActiveChart.SeriesCollection(5).XValues = rng1
ActiveChart.SeriesCollection(5).Values = rng38

Worksheets(2).ChartObjects("Set1_HSDPAPDP").Activa te
ActiveChart.SeriesCollection(1).XValues = rng1
ActiveChart.SeriesCollection(1).Values = rng39

Worksheets(2).ChartObjects("Set1_Cell").Activate
ActiveChart.SeriesCollection(1).XValues = rng1
ActiveChart.SeriesCollection(1).Values = rng40
ActiveChart.SeriesCollection(2).XValues = rng1
ActiveChart.SeriesCollection(2).Values = rng41

End Sub

"Dave Peterson" wrote:

Maybe you could get rid of 27 subroutines and replace it with this one (under
the ThisWorkbook module):

Option Explicit
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

Dim Addr1 As String
Dim Addr2 As String

Dim inRange As Range
Dim rng As Range

Addr1 = "B16,B58,T4,T15,T23,T50"
Addr2 = "B16,B58,T4,T20,T34,T50"

Select Case LCase(Sh.Name)
Case Is = "sheet1", "sheet2", "sheet3", "sheet4"
Set inRange = Sh.Range(Addr2)
Case Is = "not one of the other sheets"
'do nothing, just get out
Exit Sub
Case Else
'the other 23--too lazy to type them all out!
Set inRange = Sh.Range(Addr1)
End Select

For Each rng In inRange.Cells
If Not IsError(rng.Value) Then
If rng.Value = "c" Then
rng.Font.Name = "Wingdings 3"
Else
rng.Font.Name = "Webdings"
End If
End If
Next rng

End Sub

(I stole Rob's code and included it here, too!)


Jodi wrote:

Thanks Rob.

I believe the iserror worked. I appreciate you and kkknie helping me.

Jodi

"Rob Bovey" wrote:

Hi Jodi,

The only circumstances that I know of that would cause this error in the
code you've shown below is if one of the cells that you are looping through
contains an error value. Try placing a check for an error value prior to
testing for the character like so:

Dim Inrange As Range
Dim rng As Range
Set Inrange = Range("B16,B58,T4,T15,T23,T50")
For Each rng In Inrange.Cells
If Not IsError(rng.Value) Then
If rng.Value = "c" Then
rng.Font.Name = "Wingdings 3"
Else
rng.Font.Name = "Webdings"
End If
End If
Next rng

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Jodi" wrote in message
...
VBA Specialists,

I am receiving a run time error 13 type mismatch, with this line
highlighted:
If rng.Value = "c" Then

I have a workbook with 27 worksheets. 23 of the 27 are identical and use
the following code:
Private Sub Worksheet_Calculate()

Dim Inrange As Range
Dim rng As Range
Set Inrange = Range("B16,B58,T4,T15,T23,T50")
For Each rng In Inrange.Cells
If rng.Value = "c" Then
rng.Font.Name = "Wingdings 3"
Else
rng.Font.Name = "Webdings"
End If
Next rng

End Sub

4 of the 27 are slightly different and have a different range
Range("B16,B58,T4,T20,T34,T50")
In these 4 worksheets, cells T15 and T23 contain text strings. I know this
is causing the problem, but I want to know is there a way to easily solve
this?

The worksheets still function as designed, but a message box continuously
pops up that must be clicked and is quite annoying. Thanks for any
assistance.

Jodi




--

Dave Peterson

joel[_889_]
run time error 13 type mismatch
 

If you plot a simple chart you will see the series values are formulas
not ranges

=Sheet1!$A$2:$B$2

a formula will have an equal sign at the beginning.

You need to make the following changes

from
ActiveChart.SeriesCollection(1).XValues = rng1
ActiveChart.SeriesCollection(1).Values = rng13

to
ActiveChart.SeriesCollection(1).XValues = "=" & rng1.address
ActiveChart.SeriesCollection(1).Values = "=" & rng13.address


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=195127

http://www.thecodecage.com/forumz


All times are GMT +1. The time now is 03:11 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com