Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
run-time error 13: Type mismatch? | Excel Discussion (Misc queries) | |||
run time error 13 type mismatch | Excel Programming | |||
Run Time Error '13' Type mismatch | Excel Programming |