Excel VBA Run-time error 13 Type mismatch

Question

I created a macro for a file and first it was working fine  but today I ve been opening and restarting the file and macro hundreds of times and I m always getting the following error  Excel VBA Run-time error  13  Type mismatch  I didn t change anything in the macro and don t know why am I getting the error  Furthermore it takes ages to update the macro every time I put it running  the macro has to run about 9000 rows    The error is in the between         VBA   Sub k    Dim x As Integer  i As Integer  a As Integer Dim name As String name   InputBox  Please insert the name of the sheet   i   1 Sheets name  Cells 4  58    Sheets name  Cells 4  57  x   Sheets name  Cells 4  57  Value Do While Not IsEmpty Sheets name  Cells i   4  57       a   0     If Sheets name  Cells 4   i  57   lt  gt  x Then         If Sheets name  Cells 4   i  57   lt  gt  0 Then             If Sheets name  Cells 4   i  57    3 Then                 a   x                 Sheets name  Cells 4   i  58    Sheets name  Cells 4   i  57  - x                 x   Cells 4   i  57  - x             End If               Sheets name  Cells 4   i  58    Sheets name  Cells 4   i  57  - a               x   Sheets name  Cells 4   i  57  - a         Else         Cells 4   i  58               End If     Else     Cells 4   i  58           End If  i   i   1 Loop  End Sub   Do you think you can help me  I m using excel 2010 on windows 7  Thanks a lot

User · Answer

I had the same problem as you mentioned here above and my code was doing great all day yesterday.

I kept on programming this morning and when I opened my application (my file with an Auto_Open sub), I got the Run-time error '13' Type mismatch, I went on the web to find answers, I tried a lot of things, modifications and at one point I remembered that I read somewhere about "Ghost" data that stays in a cell even if we don't see it.

My code do only data transfer from one file I opened previously to another and Sum it. My code stopped at the third SheetTab (So it went right for the 2 previous SheetTab where the same code went without stopping) with the Type mismatch message. And it does that every time at the same SheetTab when I restart my code.

So I selected the cell where it stopped, manually entered 0,00 (Because the Type mismatch comes from a Summation variables declared in a DIM as Double) and copied that cell in all the subsequent cells where the same problem occurred. It solved the problem. Never had the message again. Nothing to do with my code but the "Ghost" or data from the past. It is like when you want to use the Control+End and Excel takes you where you had data once and deleted it. Had to "Save" and close the file when you wanted to use the Control+End to make sure Excel pointed you to the right cell.

User · Answer

Diogo  Justin has given you some very fine tips     You will also get that error if the cell where you are performing the calculation has an error resulting from a formula   For example if Cell A1 has  DIV 0  error then you will get  Excel VBA Run-time error  13  Type mismatch  when performing this code  Sheets  Sheet1   Range  A1   Value - 1   I have made some slight changes to your code  Could you please test it for me  Copy the code with the line numbers as I have deliberately put them there   Option Explicit  Sub Sample     Dim ws As Worksheet   Dim x As Integer  i As Integer  a As Integer  y As Integer   Dim name As String   Dim lastRow As Long 10        On Error GoTo Whoa  20        Application ScreenUpdating   False  30        name   InputBox  Please insert the name of the sheet    40        If Len Trim name     0 Then Exit Sub  50        Set ws   Sheets name   60        With ws 70            If Not IsError  Range  BE4   Value  Then 80                x   Val  Range  BE4   Value  90            Else 100               MsgBox  Please check the value of cell BE4  It seems to have an error  110               GoTo LetsContinue 120           End If  130            Range  BF4   Value   x  140           lastRow    Range  BE   amp  Rows Count  End xlUp  Row  150           For i   5 To lastRow 160               If IsError  Range  BE   amp  i   Then 170                   MsgBox  Please check the value of cell BE   amp  i  amp     It seems to have an error  180                   GoTo LetsContinue 190               End If  200               a   0  y   Val  Range  BE   amp  i   210               If y  lt  gt  x Then 220                   If y  lt  gt  0 Then 230                       If y   3 Then 240                           a   x 250                            Range  BF   amp  i    Val  Range  BE   amp  i   - x  260                           x   Val  Range  BE   amp  i   - x 270                       End If 280                        Range  BF   amp  i    Val  Range  BE   amp  i   - a 290                       x   Val  Range  BE   amp  i   - a 300                   Else 310                        Range  BF   amp  i  ClearContents 320                   End If 330               Else 340                    Range  BF   amp  i  ClearContents 350               End If 360           Next i 370       End With  LetsContinue  380       Application ScreenUpdating   True 390       Exit Sub Whoa  400       MsgBox  Error Description     amp  Err Description  amp  vbNewLine  amp              Error at line          amp  Erl 410       Resume LetsContinue End Sub

User · Answer

This error occurs when the input variable type is wrong  You probably have written a formula in Cells 4   i  57  that instead of  0  the formula      have used  So when running this error is displayed  Because empty string is not equal to zero

User · Answer

For future readers   This function was abending in Run-time error  13   Type mismatch  Function fnIsNumber Value  As Boolean   fnIsNumber   Evaluate  ISNUMBER 0      amp  Value  amp         End Function   In my case  the function was failing when it ran into a  DIV 0  or N A value   To solve it  I had to do this   Function fnIsNumber Value  As Boolean    If CStr Value     Error 2007  Then   lt       This is the important line       fnIsNumber   False    Else       fnIsNumber   Evaluate  ISNUMBER 0      amp  Value  amp            End If End Function

User · Answer

Thank you guys for all your help  Finally I was able to make it work perfectly thanks to a friend and also you  Here is the final code so you can also see how we solve it   Thanks again    Option Explicit  Sub k    Dim x As Integer  i As Integer  a As Integer Dim name As String  name   InputBox  Please insert the name of the sheet   i   1 name    Reserva  Sheets name  Cells 4  57    Sheets name  Cells 4  56   On Error GoTo fim x   Sheets name  Cells 4  56  Value Application Calculation   xlCalculationManual Do While Not IsEmpty Sheets name  Cells i   4  56       a   0     If Sheets name  Cells 4   i  56   lt  gt  x Then         If Sheets name  Cells 4   i  56   lt  gt  0 Then             If Sheets name  Cells 4   i  56    3 Then                 a   x                 Sheets name  Cells 4   i  57    Sheets name  Cells 4   i  56  - x                 x   Cells 4   i  56  - x             End If             Sheets name  Cells 4   i  57    Sheets name  Cells 4   i  56  - a             x   Sheets name  Cells 4   i  56  - a         Else         Cells 4   i  57               End If     Else     Cells 4   i  57           End If  i   i   1 Loop Application Calculation   xlCalculationAutomatic Exit Sub fim  MsgBox Err Description Application Calculation   xlCalculationAutomatic End Sub

User · Answer

You would get a type mismatch if Sheets name  Cells 4   i  57  contains a non-numeric value  You should validate the fields before you assume they are numbers and try to subtract from them   Also  you should enable Option Strict so you are forced to explicitly convert your variables before trying to perform type-dependent operations on them such as subtraction  That will help you identify and eliminate issues in the future  too   nbsp  nbsp  nbsp Unfortunately Option Strict is for VB NET only  Still  you should look up best practices for explicit data type conversions in VBA     Update   If you are trying to go for the quick fix of your code  however  wrap the    line and the one following it in the following condition   If IsNumeric Sheets name  Cells 4   i  57       Sheets name  Cells 4   i  58    Sheets name  Cells 4   i  57  - a     x   Sheets name  Cells 4   i  57  - a End If   Note that your x value may not contain its expected value in the next iteration  however

User · Answer

Sub HighlightSpecificValue     PURPOSE  Highlight all cells containing a specified values   Dim fnd As String  FirstFound As String Dim FoundCell As Range  rng As Range Dim myRange As Range  LastCell As Range   What value do you want to find    fnd   InputBox  I want to hightlight cells containing       Highlight         End Macro if Cancel Button is Clicked or no Text is Entered       If fnd   vbNullString Then Exit Sub  Set myRange   ActiveSheet UsedRange Set LastCell   myRange Cells myRange Cells Count   enter code here Set FoundCell   myRange Find what  fnd  after  LastCell    Test to see if anything was found   If Not FoundCell Is Nothing Then     FirstFound   FoundCell Address    Else     GoTo NothingFound   End If  Set rng   FoundCell   Loop until cycled through all unique finds   Do Until FoundCell Is Nothing      Find next cell with fnd value       Set FoundCell   myRange FindNext after  FoundCell              Add found cell to rng range variable       Set rng   Union rng  FoundCell        Test to see if cycled through to first found cell       If FoundCell Address   FirstFound Then Exit Do     Loop   Highlight Found cells yellow    rng Interior Color   RGB 255  255  0     Dim fnd1 As String   fnd1    Rah     Condition highlighting    Set FoundCell   myRange FindNext after  FoundCell       If FoundCell Value  rah   Then       rng Interior Color   RGB 255  0  0     ElseIf FoundCell Value  Nav   Then      rng Interior Color   RGB 0  0  255         End If       Report Out Message   MsgBox rng Cells Count  amp    cell s  were found containing     amp  fnd  Exit Sub   Error Handler NothingFound    MsgBox  No cells containing     amp  fnd  amp    were found in this worksheet   End Sub

[excel] Excel VBA Run-time error '13' Type mismatch

Examples related to excel

Examples related to excel-2010

Examples related to vba