Home > Blockchain >  when populating cells I get a Runtime error 13 after running for a few cells
when populating cells I get a Runtime error 13 after running for a few cells

Time:05-18

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 …
  • Related