Excel VBA Run-time error '13' Type mismatch

diogoo

New Member
Joined
Jan 16, 2012
Messages
4
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

diogoo

New Member
Joined
Jan 16, 2012
Messages
4
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

Well-known Member
Joined
Jul 9, 2009
Messages
1,894
Office Version
  1. 2013
Platform
  1. Windows
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:
[COLOR=#ff0000][COLOR=black]Sheets(name).Cells(4 + i, 58)[/COLOR] [COLOR=black]= Sheets(name).[/COLOR]Cells(4 + i, 57) - a[/COLOR]
 

diogoo

New Member
Joined
Jan 16, 2012
Messages
4

ADVERTISEMENT

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:
[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:


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
 

Crystal Wang

New Member
Joined
Nov 27, 2012
Messages
2
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

Well-known Member
Joined
Jul 9, 2009
Messages
1,894
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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?


 

Nouman229

New Member
Joined
Aug 5, 2015
Messages
2
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

New Member
Joined
Aug 5, 2015
Messages
2
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
 

Forum statistics

Threads
1,148,427
Messages
5,746,618
Members
424,033
Latest member
al1en

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top