Excel VBA Runtime Error '13' Type Mismatch error

Question

I am having problems with the definition of my variables I think but I cannot see where or why. It's quite a simple code to count the amount of lessons teachers have allocated. The information is in the worksheet 'Subects and Teachers 2018' and has to be printed in the worksheet 'Teachers'. The quantities always appear on the left of the name.

Here's the code. If anyone could give me a hint on what I'm defining incorrectly I would be very thankful! Debugging suggests that the problem is in the line which has ***** at the end (not part of the code).

Sub Counter2018()
    Dim Var1 As String
    Dim CVar1 As Integer

    Dim i As Integer
    Dim j As Integer
    Dim k As Integer

    For k = 2 To 50
        Var1 = Worksheets("Teachers").Cells(k, 3)
        CVar1 = 0
        For i = 2 To 45
            For j = 2 To 45
                If Worksheets("2018 Subjects and Teachers").Cells(i, j) = Var1 Then
                    CVar1 = CVar1 + Worksheets("2018 Subjects and Teachers").Cells(i, j - 1) *****
                End If
            Next j
        Next i
        Worksheets("Teachers").Cells(k, 5) = CVar1
    Next k
End Sub

Answer 1

Try this version (untested)

---

Option Explicit

Public Sub Counter2018()
    Dim wsTeachers As Worksheet, wsSubjects As Worksheet

    Set wsTeachers = Worksheets("Teachers")
    Set wsSubjects = Worksheets("2018 Subjects and Teachers")

    Dim teacher As String, counter As Long
    Dim i As Long, j As Long, k As Long

    For k = 2 To 50
        teacher = wsTeachers.Cells(k, 3)
        counter = 0
        For i = 2 To 45
            For j = 2 To 45
                If wsSubjects.Cells(i, j).Value2 = teacher Then
                    If Not IsError(wsSubjects.Cells(i, j - 1)) Then
                        counter = counter + Val(wsSubjects.Cells(i, j - 1).Value2)
                    End If
                End If
            Next
        Next
        wsTeachers.Cells(k, 5) = counter
    Next
End Sub

---

I think the Type Mismatch error is caused by some of the cells in wsSubjects.Cells(i, j - 1)

That counter expects numbers in that column, but there might be some strings or errors in there