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](https://stackoverflow.com/questions/8885506/excel-vba-run-time-error-13-type-mismatch) — Capricorn, Sep 04 '18 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, Sep 04 '18 at 16:05

Vityata · Accepted Answer · 2018-09-04T21:23:00.553

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 04 '18 at 21:23

answered Sep 04 '18 at 16:02

Vityata

42,633
8
55
100

1

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. :) – Darren Bartrup-Cook Sep 04 '18 at 16:05
@DarrenBartrup-Cook - still can mention the complete referring to `Worksheet()` problem :D – Vityata Sep 04 '18 at 16:06
Looking for someone to mention error handler and the "bad smell" from the `Exit Sub` in the middle. :D – Vityata Sep 04 '18 at 18:20
1

Thanks! This is my first time coding for work. I appreciate the help! y'all are awesome – jojo Sep 04 '18 at 19:33
@jojo - you are welcome! :) Consider marking this answer as an accepted https://stackoverflow.com/help/someone-answers – Vityata Sep 04 '18 at 19:41
Yes of course! By the way, why did I need to change it to Long? – jojo Sep 04 '18 at 21:13
@jojo - https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long – Vityata Sep 04 '18 at 21:22
@jojo - consider putting your code here - https://codereview.stackexchange.com, you may get some useful ideas. – Vityata Sep 04 '18 at 21:24
1

I'll take a look. I appreciate all the help:D – jojo Sep 04 '18 at 21:30

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

1 Answers1