Run-time error 13 in my VBA code when generating batch number

Question

I'm having trouble with the datatype in my code. I converted the 6 to a string but I'm getting the same error. Any suggestions please?

Sub test1()
' Generates batch number based on company code

Dim x As Integer
Dim a As Integer    

a = Cells(Rows.Count, 2).End(xlUp).Row    

For x = 6 To a    
    If Cells(x, 2).Value = "US1K" Then
        Cells(x, 1).Value = 1

    ElseIf Cells(x, 2).Value = "USYB" Then
        Cells(x, 1).Value = 2

    ElseIf Cells(x, 2).Value = "US1A" Then
        Cells(x, 1).Value = 3

    ElseIf Cells(x, 2).Value = "US4U" Then
        Cells(x, 1).Value = 4

    ElseIf Cells(x, 2).Value = "US3D" Then
        Cells(x, 1).Value = 5
 Else
        Cells(x, 1).Value = " "

    End If
   Next x
End Sub

Possible duplicate of Excel VBA Run-time error '13' Type mismatch — Capricorn
– Capricorn, Commented Sep 4, 2018 at 16:00
@Capricorn - it is a bit similar, but far away from a duplicate. The OP is writing to a cell, which can values which are non-numeric as well. — Vityata
– Vityata, Commented Sep 4, 2018 at 16:05

Vityata · Accepted Answer · 2018-09-04 21:23:00Z

0

Most probably one of the cells is with an error. Try only this part and see whether you get a MsgBox(). Then, incorporate the IsError() part in your code:

Sub TestMe()

    Dim x As Long
    Dim a As Long

    a = Cells(Rows.Count, 2).End(xlUp).Row

    For x = 6 To a
        If IsError(Cells(x, 2)) Then
            MsgBox Cells(x, 2).Address
            Exit Sub
        End If
        'do the rest of your code here
    Next x

End Sub

Why Use Integer Instead of Long?

edited Sep 4, 2018 at 21:23

answered Sep 4, 2018 at 16:02

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

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

9 Comments

Darren Bartrup-Cook Over a year ago

You keep beating me to it by a couple of seconds - was going to mention the Integer to Long problem and then add a comment here about a cell containing an error.... ah well, never mind. :)

Vityata Over a year ago

@DarrenBartrup-Cook - still can mention the complete referring to Worksheet() problem :D

Vityata Over a year ago

Looking for someone to mention error handler and the "bad smell" from the Exit Sub in the middle. :D

jojo Over a year ago

Thanks! This is my first time coding for work. I appreciate the help! y'all are awesome

jojo Over a year ago

I'll take a look. I appreciate all the help:D

|

Collectives™ on Stack Overflow

Run-time error 13 in my VBA code when generating batch number

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related