Excel VBA Run-time error '13' Type mismatch

[diogoo]

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

 

[diogoo]

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?

 

[daverunt]

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.

[COLOR=#ff0000][COLOR=black]Sheets(name).Cells(4 + i, 58)[/COLOR] [COLOR=black]= Sheets(name).[/COLOR]Cells(4 + i, 57) - a[/COLOR]

 

[diogoo]

its allways a number between 0 and 3.
:s

 

[diogoo]

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.

[COLOR=#ff0000][COLOR=black]Sheets(name).Cells(4 + i, 58)[/COLOR] [COLOR=black]= Sheets(name).[/COLOR]Cells(4 + i, 57) - a[/COLOR]

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:

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

 

[Crystal Wang]

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!

 

[daverunt]

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?

 

[Crystal Wang]

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

 

[Nouman229]

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

 

[Nouman229]

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