Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Excel VBA Run-time error '13' Type mismatch

  1. #1
    New Member
    Join Date
    Jan 2012
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel VBA Run-time error '13' Type mismatch

    Hi!

    I created a macro for a file and first it was working fine, but today I've been oppening and restarting the file and macro hundreds of times and I'm allways guetting the following error: Excel VBA Run-time error '13' Type mismatch

    I didn't change anything in the macro so dont know why am I gueting the error. Furthermore it takes ages to update the macro everytime I put it running (the macro has to run about 9000 rows).

    The error is in the in red part.


    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) <> x Then
    If Sheets(name).Cells(4 + i, 57) <> 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

  2. #2
    New Member
    Join Date
    Jan 2012
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Run-time error '13' Type mismatch

    I've searched on several websites but none of the "Run-time error '13' Type mismatch" was like this one...
    Anyone with any idea how to solve it?

  3. #3
    Board Regular
    Join Date
    Jul 2009
    Posts
    1,352
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Run-time error '13' Type mismatch

    Hi,

    This problem would ocur if the cell content in red was not a number as you are trying to subtract 'a' - an integer from it.
    Other than that I can't see a problem.

    Comment out the '- a' and see if the type mismatch moves to the next line.


    Code:
    Sheets(name).Cells(4 + i, 58) = Sheets(name).Cells(4 + i, 57) - a

  4. #4
    New Member
    Join Date
    Jan 2012
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Run-time error '13' Type mismatch

    its allways a number between 0 and 3.
    :s

  5. #5
    New Member
    Join Date
    Jan 2012
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Run-time error '13' Type mismatch

    Quote Originally Posted by daverunt View Post
    Hi,

    This problem would ocur if the cell content in red was not a number as you are trying to subtract 'a' - an integer from it.
    Other than that I can't see a problem.

    Comment out the '- a' and see if the type mismatch moves to the next line.


    Code:
    Sheets(name).Cells(4 + i, 58) = Sheets(name).Cells(4 + i, 57) - a
    Thanks daverunt for your help.
    Finally I was able to make the code work thanks to the help of a friend. Just for your info it last version is:


    Code:
    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) <> x Then
            If Sheets(name).Cells(4 + i, 56) <> 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

  6. #6
    New Member
    Join Date
    Nov 2012
    Location
    Hong Kong
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Re: Excel VBA Run-time error '13' Type mismatch

    Hi!
    I am new in VBA and I have the same error as you when running my code. the code segment is here:


    Dim k As Variant
    Dim j As Integer
    Dim m As Variant
    Dim n As Variant


    m = 0


    For j = 1 To 4


    m = "&2j&" + 1 'here the error comes: type mismatch
    n = "&2j&" + 2
    If k = m Then

    Cells("&(NumRow + 2+18j)&", 2).Select
    ActiveCell.FormulaR1C1 = "Actin-P1(25uM)"

    Cells("&(NumRow + 3+18j)&", 2).Select
    ActiveCell.FormulaR1C1 = "Actin-P2(25uM) "

    Cells("&(NumRow + 4+18j)&", 2).Select
    ActiveCell.FormulaR1C1 = "GAPDH-P1(25uM)"





    if k=n then


    Range("C" & (38 + "18j&") & ": F" & (54 + "18j&")).Select

    Selection.Cut
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.SmallScroll Down:=-6
    Range("P" & (3 + "18j&")).Select
    ActiveSheet.Paste



    End If

    waiting for your help. thanks so much!

  7. #7
    Board Regular
    Join Date
    Jul 2009
    Posts
    1,352
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Run-time error '13' Type mismatch

    Integer m = string "&2j&" + integer 1

    You are tring to add a number to a string of characters - different 'types' and therefore 'Type Mismatch'


    if m=0 and j=1?

    what value do you expect from this "&2j&"


    Is it supposed to be m= 2 * j +1 =3?



  8. #8
    New Member
    Join Date
    Nov 2012
    Location
    Hong Kong
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Run-time error '13' Type mismatch

    YES,what I want to say is that if m is odd, then go to the if statement.

  9. #9
    New Member
    Join Date
    Aug 2015
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Run-time error '13' Type mismatch

    Hi,
    i want to copy values of cells from column 13 and 6 to column 9 based on value in corresponding cell in column 4. i'm getting the following error, run time error 13 type mismatch. kindly help me what's wrong in red and i'm sure same error will be received on blue as well. Please help.

    Sub CRM_VAS()
    '
    Dim LS As Range
    Dim PN As Range
    Dim PC As Range
    Dim CPN As Range


    Set LS = Range("D2:D50")
    Set PN = Range("F2:F50")
    Set PC = Range("I2:I50")
    Set CPN = Range("M2:M50")


    For Each x In LS
    If x.Value = "CRM" Then
    PC.Cells(x, 9) = CPN.Cells(x, 13)
    End If

    If x.Value = "VAS" Then
    PC.Cells(x, 9) = PN.Cells(x, 6)
    End If
    Next


    End Sub

  10. #10
    New Member
    Join Date
    Aug 2015
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Run-time error '13' Type mismatch

    Hi,

    i am executing below macro code but it is giving me run time error 13 type mismatch during the execution. it runs successfully from row 2 to row 34 but then gives run time error 13 type mismatch. kindly help me in rectifying this issue. error on line is highlighted in red below.

    Sub Modification()


    Dim PC As Range
    Dim LS As Range
    Dim CPN As Range


    Set PC = Range("I2:I50")
    Set LS = Range("D2:D50")
    Set CPN = Range("M2:M50")


    Range("M2:M50").Copy Destination:=Range("I2:I50")


    For Each y In LS
    If y.Value = "CRM" Then
    PC.Value = Replace(PC.Value, " - ", "_")
    PC.Value = Replace(PC.Value, "-", "_")
    PC.Value = Replace(PC.Value, " ", "_")
    Else
    If y.Value = "IN" Then
    Dim oWS As Worksheet, lLastRow As Long, r As Long


    Set oWS = ActiveSheet
    lLastRow = oWS.Cells.SpecialCells(xlLastCell).Row
    For r = 2 To lLastRow
    ' Combine if both C and D are not empty
    If Len(oWS.Cells(r, 8)) > 0 And Len(oWS.Cells(r, 7)) > 0 Then
    oWS.Cells(r, 9).Value = oWS.Cells(r, 7).Value & " " & oWS.Cells(r, 8).Value
    End If
    Next
    End If
    End If
    Next y


    'IF(D2="CRM",M2,IF(D2="VAS",F2,IF(D2="IN",CONCATENATE(G2,"_",H2),NULL)))


    For Each x In Range("I2:I50")
    ' Change the text in the range to uppercase letters.
    x.Value = UCase(x.Value)
    Next


    End Sub

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com