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

 

[IgorPacheco]

Hi,
I have the same error, when i'm running the code.
The code segment is:

Ref = 1
K = 1
For K = 1 To Nmes
I = 1
Do
If Cells(4 + I, 1) = "" Then
Exit Do
End If
If K = 1 Then
If Cells(4 + I, 5) = "" And (Cells(4 + I, 7) + Cells(4 + I, 7)) > (Cells(4 + I, 11) * (Max + 1)) And Cells(4 + I, 13) > 0 Then
Cod(Ref) = Cells(4 + I, 1)
If Cells(4 + I, 11) = 0 Then
Cob_Inic(Ref) = 999
Else
Cob_Inic(Ref) = ((Cells(4 + I, 7) + Cells(4 + I, 8)) / Cells(4 + I, 11))
End If
Qtd(Ref) = Cells(4 + I, 13)
Cob_Fin(Ref) = Cells(4 + I, 15)
Mat(Ref) = Cells(4 + I, 4)
Orig(Ref) = Cells(4 + I, 6)
Mes(Ref) = Cells(2, 11 + K * 4 - 3)
accao(Ref) = "Adiar " & Int(Cob_Inic(Ref) - (Max)) & " Meses"
Ref = Ref + 1
End If

If Cells(4 + I, 5) = "" And (Cells(4 + I, 7) + Cells(4 + I, 7)) < (Cells(4 + I, 11) * (Min + 1)) And Cells(4 + I, 13) > 0 Then
Cod(Ref) = Cells(4 + I, 1)
If Cells(4 + I, 11) = 0 Then
Cob_Inic(Ref) = 999
Else
Cob_Inic(Ref) = ((Cells(4 + I, 7) + Cells(4 + I, 7)) / Cells(4 + I, 11))
End If
Qtd(Ref) = Cells(4 + I, 13)
Cob_Fin(Ref) = Cells(4 + I, 15)
Mat(Ref) = Cells(4 + I, 4)
Orig(Ref) = Cells(4 + I, 6)
Mes(Ref) = Cells(2, 11 + K * 4 - 3)
accao(Ref) = "Antecipar"
Ref = Ref + 1
End If

 

[Eddiekash]

Hi I'm new to VBA. can someone help me with this script I got from the board. I've tried to run it but all I get is run-time error '13.

Public Sub ExpandRecords()
Dim i As Long, _
j As Long, _
LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Columns("D:D").NumberFormat = "@"
For i = LR To 1 Step -1
If CLng(Right(Range("D" & i).Value, Len(Range("D" & i).Value) - 3)) > 1 Then
With Range("A" & i)
.Offset(1, 0).Resize(CLng(Right(Range("D" & i).Value, Len(Range("D" & i).Value) - 3)) - 1, 1).EntireRow.Insert Shift:=xlDown
.Resize(CLng(Right(Range("D" & i).Value, Len(Range("D" & i).Value) - 3)), 1).EntireRow.Value = Range("A" & i).EntireRow.Value
For j = 1 To CLng(Right(Range("D" & i).Value, Len(Range("D" & i).Value) - 3))
Range("D" & i).Offset(j - 1, 0).Value = Application.Text(j, "000")
Next j
End With
Else
Range("D" & i).Value = Application.Text(1, "000")
End If
Next i
Application.ScreenUpdating = True
End Sub

 

[IgorPacheco]

hi! can someone tell me what is wrong with this line of code?

If Cells(4 + I, 5) = "" And (Cells(4 + I, 7) + Cells(4 + I, 7)) > (Cells(4 + I, 11) * (Max + 1)) And Cells(4 + I, 13) > 0 Then

 

[KimberlyHeart]

hi! can someone tell me what is wrong with this line of code?

If Cells(4 + I, 5) = "" And (Cells(4 + I, 7) + Cells(4 + I, 7)) > (Cells(4 + I, 11) * (Max + 1)) And Cells(4 + I, 13) > 0 Then

If Cells(4 + I, 5) = 0

 

[dashiboy]

Hello Guys

I am facing problem run time error 13 type mismatch see below image any one please help please to solve my problem. THANKS


-- removed inline image ---

 

[dashiboy]

Here is the code

Sub NextInvoice()
OldInv = Range("I8").Value
Cust = Left(OldInv, 4)
InvNumber = Mid(OldInv, 5, 4) + 1
InvYear = Right(OldInv, 3)
Range("I8").Value = Cust & InvNumber & InvYear
Range("B21:H40").ClearContents

'SaveWithNewName

Dim NewFN As Variant
'Copy Invoice to a new workbook
ActiveSheet.Copy
NewFN = "D:\IHS Invoices\" & Range("B9 , I9") & Range("I8").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close

' AttachActiveSheetPDF_01()
Dim IsCreated As Boolean
Dim PdfFile As String, Title As String
Dim OutlApp As Object

' Not sure for what the Title is
Title = Range("I8")

' Define PDF filename
Title = Range("B9 , I9") & Range("I8").Value
PdfFile = "D:\IHS Invoices\" & Title & ".pdf"

' Export activesheet as PDF
With ActiveSheet
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With

' Use already open Outlook if possible
On Error Resume Next
Set OutlApp = GetObject(, "Outlook.Application")
If Err Then
Set OutlApp = CreateObject("Outlook.Application")
IsCreated = True
End If
OutlApp.Visible = True
On Error GoTo 0

' Prepare e-mail with PDF attachment
With OutlApp.CreateItem(0)

' Prepare e-mail
.Subject = Title
.To = " " ' <-- Put email of the recipient here
.BCC = " " ' <-- Put email of 'copy to' recipient here
.Body = "Hi," & vbLf & vbLf _
& "Please see attached your latest invoice in PDF format." & vbLf & vbLf _
& "Regards," & vbLf _
& Application.UserName & vbLf & vbLf

.Attachments.Add PdfFile

' Try to send
Application.Visible = True
.Display
End With

' Quit Outlook if it was not already open
If IsCreated Then OutlApp.Quit

' Release the memory of object variable
Set OutlApp = Nothing

End Sub

 

[kenpcli]

Having the same issue:

Dim LR3 As Long, i3 As Long
With Sheets("CA Site Sub Total").Select

LR3 = Range("A" & rows.Count).End(xlUp).row


For i3 = LR3 To 6 Step -1
If Not IsNumeric(Range("A" & i3).Value) Or _
Range("A" & i3).Value = "" Then rows(i3).delete
Next i3
End With

 

[SandsFoy]

Hi - I'm trying to delete any rows of data that have #N/A in column CA (This was a vlookup, but have since done a copy past special values to make text). I've done like this,

I'm getting the Runtime 13 error on this line,

If Rage("CA" & I) = "#N/A" Then

The next line then delets the row - any idea what is causng the error?
Thank you!

 

[Fluff]

Try

If IsError(Range("CA" & I)) Then

 

[SandsFoy]

That worked a treat! Thank you