when populating cells I get a Runtime error 13 after running for a few cells-CodePudding

I am trying to build a code that will help me populate a list with the team names based on 2 different tables in another worksheet.

The code actually runs quite well for the first 10 cells, and then I suddenly get a Runtime error 13 "Type mismatch" and I cannot figure what is wrong with it

the code I have is

Sub populateteam()

Dim wsAkasaka As Worksheet
Dim wsList As Worksheet

Set wsAkasaka = ThisWorkbook.Worksheets("Akasaka")
Set wsList = ThisWorkbook.Worksheets("All Japan")

Dim consultant As String
Dim manager As String
Dim team As String

consultant = wsAkasaka.Range("b" & (ActiveCell.Row)).Value
manager = Application.VLookup(consultant, wsList.Range("a13:c200"), 3, False)
team = Application.VLookup(manager, wsList.Range("E2:F11"), 2, False)

If IsEmpty(ActiveCell.Value) Then
ActiveCell.Value = team
ActiveCell.Offset(1, 0).Select

End If


End Sub

If anyone could give me a light for why this is happening and how could I fix it.

Edit I forgot to write I get the error on the "team" declaration, I took it out and ran the code populating with "manager" and had no problem with it, but when I ask for the team I get the error

CodePudding user response：

The issue is that you define your variables As String and if your VLookup returns an error (in case VLookup doesn't find anything), this error cannot be cast into a String and you get a Mismatch Error.

Therefore I recommend the following:

' your code here …

Dim consultant As String
consultant = wsAkasaka.Range("b" & (ActiveCell.Row)).Value

Dim manager As Variant
manager = Application.VLookup(consultant, wsList.Range("a13:c200"), 3, False)

If IsError(manager) Then  ' check if consultant was found, if not exit
    MsgBox "Consultant """ & consultant & """ not found."
    Exit Sub
End If

Dim team As Variant
team = Application.VLookup(manager, wsList.Range("E2:F11"), 2, False)

If IsError(team) Then  ' check if manager was found, if not exit
    MsgBox "Manager """ & manager & """ not found."
    Exit Sub
End If

' your code here …