VBA Excel run time error 13 type mismatch

Question

I have an issue with the InputBox. my macro was working well. and this is my code so i find on google that if the CInt function is supplied with a text string that cannot be converted into a numeric value, it will return the error, is it true? or any advise?

Private Sub btn_check_Click()
Application.ScreenUpdating = False

Dim a, b, x, y, z, c As Long
Dim indicator, puzzlePartner, puzzleRaw As String

a = 5
b = 2
c = 5

Do Until Cells(c, 1) = ""
     Sheet9.Cells(c, 3).Select
        Sheet9.Cells(c, 56) = Trim(CStr(Left(Sheet9.Cells(c, 3), 15)))
    c = c + 1
    Loop

a = 5

Do Until Sheet9.Cells(a, 1) = ""
    Do Until Sheet4.Cells(b, 104) = ""
        If a = 2000 Then
        c = 1
        End If
    If Sheet4.Cells(b, 9) = Sheet9.Cells(a, 9) And Sheet4.Cells(b, 27) = Sheet9.Cells(a, 27) And Sheet4.Cells(b, 26) = Sheet9.Cells(a, 26) And _
         Sheet4.Cells(b, 25) = Sheet9.Cells(a, 25) And Sheet4.Cells(b, 24) = Sheet9.Cells(a, 24) And Sheet9.Cells(a, 52) = Sheet4.Cells(b, 104) And _
         LCase(Trim(Sheet4.Cells(b, 21))) = LCase(Trim(Sheet9.Cells(a, 21))) Then
         Sheet9.Cells(a, 53) = "True"
        GoTo 1
    ElseIf CInt(Cells(2, 3)) <> Year(Sheet9.Cells(a, 9)) And Sheet9.Cells(a, 15) = "Withdrawn" Then
        Sheet9.Cells(a, 53) = "True"
        GoTo 1
    ElseIf CInt(Cells(2, 3)) <> Year(Sheet9.Cells(a, 9)) And Sheet9.Cells(a, 15) = "DC" Then
        Sheet9.Cells(a, 53) = "True"
        GoTo 1
    ElseIf CInt(Cells(2, 3)) <> Year(Sheet9.Cells(a, 9)) And Sheet9.Cells(a, 15) = "PO" Then
        Sheet9.Cells(a, 53) = "True"
        GoTo 1
    ElseIf Right(Sheet9.Cells(a, 7), 3) = "(S)" Then
        Sheet9.Cells(a, 53) = "True"
        GoTo 1
     ElseIf CInt(Cells(2, 3)) <> Year(Sheet9.Cells(a, 51)) And Sheet9.Cells(a, 15) = "APP" Then
        Sheet9.Cells(a, 53) = "True"
        GoTo 1
    ElseIf CInt(Cells(2, 3)) <> Year(Cells(a, 12)) And Right(Cells(a, 52), 3) = "RNW" Then
        Sheet9.Cells(a, 53) = "True"
        GoTo 1
        End If
    End If
2:
    b = b + 1
    Loop
            Sheet9.Cells(a, 53) = "False"
x = CInt(Len(Sheet9.Cells(a, 52)))
            y = x - 4
            indicator = Mid(Sheet9.Cells(a, 52), y, 1)
            If Sheet9.Cells(a, 53) = "False" And indicator = "-" Then
                Sheet9.Cells(a, 53) = "Top-up"
          End If
1:

    b = 2
a = a + 1
Loop

I have also noticed that this error is shown on the line

     ElseIf CInt(Cells(2, 3)) <> Year(Sheet9.Cells(a, 51)) And Sheet9.Cells(a, 15) = "APP" Then

Anyone have idea why is this happening?

This: Dim a, b, x, y, z, c As Long doesn't do what you think it does. c is defined as a long, but all the other ones are defined as Variant - That is not a shortcut to Dimming variables of the same type.And the same goes for Dim indicator, puzzlePartner, puzzleRaw As String — braX
– braX, Commented Feb 24, 2020 at 11:06
And do not use GoTo - it creates a mess out of your code. Use simple logic structures instead. — braX
– braX, Commented Feb 24, 2020 at 11:09
yes thanks for info, but the point is this code just read year (2020), and not read code <> Year(Sheet9.Cells(a, 51)) because input year was blank. thats why show type mismatch — rickydinataa
– rickydinataa, Commented Feb 25, 2020 at 6:48
i think the problem is not the CInt, the problem code not read <> Year(Sheet9.Cells(a, 51)) because reflect value " " in input file, I dont fill the value, and how to add statement elseif if number <> blank value? any advise sir/madam? — rickydinataa
– rickydinataa, Commented Feb 25, 2020 at 6:50

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Yes, the CInt method returns a Runtime error when passed a non numeric value. You can use

IsNumeric(SheetName.Cells(2, 3))

method to perform a type check before using CInt. IsNumeric will return you true or false based on the type of input passed.

Update

In case of date type you can use IsDate() method to validate that the cell value is of date type or not. Only after type check use Year() method.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Feb 24, 2020 at 10:11

stud3nt

2,1811 gold badge14 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Teamothy Over a year ago

Also specify from which sheet this Cells(2,3) is, in other way it can take wrong values.

rickydinataa Over a year ago

i think the problem is not the CInt, the problem code not read <> Year(Sheet9.Cells(a, 51)) because reflect value " " any advise sir/madam?

rickydinataa Over a year ago

In my opinion the problem code not read <> Year(Sheet9.Cells(a, 51)) because reflect value ("") I don't fill any value on InputBox which mean ("") and my question, how to add statement elseif number <> blank value ("")?

rickydinataa Over a year ago

Cells(2,3) output was "2020" and <> Year(Sheet9.Cells(a, 51)) output was ""

Collectives™ on Stack Overflow

VBA Excel run time error 13 type mismatch

1 Answer 1

Update

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Update

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related