excel - Receiving an Type Mismatch error while running the code

Question

I have been using this code to get the data from Sheets("Data") code goes and search the first header i.e. Gross Wage then Gross Label and then Gross DD. IF these headers are match then code will copy the values (available in Sheets("Data") row 7,8 and 9) then paste the values in Sheets("Final").Range(G11,H11 and I11) via transpose.

Pasting sequence is that Sheets("Final") Columns C and D have years and months that are change according to the D5 date. And Sheets("Data") row 6 has years and quarters which values pasted in the Sheets("Final").

Now the problem is that it is giving an type mismatch error. Can someone please look into this issue. Why the error is occuring.

[![enter image description here][1]][1]

Your help will highly appreciated.

Sub input()

Dim x, y, z, i As Long, ii As Long, iii As Long, iv As Long, lRow As Long
    Dim Output, sCohort As String, lCS As Long, lCE As Long, lMnth As Long
    Dim Hdrs, iGp As Integer, iGdb As Integer, iGsb As Integer

    x = Application.Transpose(Sheet1.[e7].Resize(3))
    For i = LBound(x) To UBound(x)
        If Trim(Split(LCase(x(i)), "-")(1)) = "Gross Wage" Then
            iGp = i
        ElseIf Trim(Split(LCase(x(i)), "-")(1)) = "Gross Label" Then
            iGdb = i
        ElseIf Trim(Split(LCase(x(i)), "-")(1)) = "Gross DD" Then
            iGsb = i
        End If
    Next
    
    lRow = Sheet1.Cells(Rows.Count, 5).End(xlUp).Row
    lCS = Application.Match("Row", Sheet1.Rows(6), 0)
    lCE = Application.Match("Run*", Sheet1.Rows(6), 0)
    x = Sheet1.Cells(6, lCS).Resize(lRow - 5, lCE - lCS)
    y = Sheets("Final").[b11].CurrentRegion
    ReDim z(1 To UBound(y, 1), 1 To 3)
    sCohort = Sheet1.Range("B7")
    lMnth = Sheets("Final").[d11]
    
    Select Case lMnth Mod 3
        
        Case Is = 0: ii = 0
        Case Is = 1: ii = 2
        Case Is = 2: ii = 1
    
    End Select
    
    Select Case lMnth
        
        Case Is < 4: iv = 5
        Case Is < 7: iv = 6
        Case Is < 10: iv = 7
        Case Is < 13: iv = 4
    
    End Select
    
    iii = Application.Choose(lMnth, 7, 6, 5, 4, 3, 2, 1, 0, -1, 10, 9, 8)
    
    If sCohort = vbNullString Then Exit Sub
    
    For i = iv To UBound(x, 2)
        
        If x(1, i) Like "Q*" Then
            ii = ii + 3
            z(ii, 1) = x(iGp + 1, i)
            z(ii, 2) = x(iGdb + 1, i)
            z(ii, 3) = x(iGsb + 1, i)
            
        ElseIf x(1, i) Like "Y*" Then
            iii = iii + 12
            z(iii, 1) = x(iGp + 1, i)
            z(iii, 2) = x(iGdb + 1, i)
            z(iii, 3) = x(iGsb + 1, i)
            
        End If
    Next
    
    Sheets("Final").[g10].Resize(UBound(z, 1), UBound(z, 2)) = z
        
End Sub

question from:https://stackoverflow.com/questions/66046618/receiving-an-type-mismatch-error-while-running-the-code

Answer 1

You can handle the error in this way:

If Not IsError(Application.match("Row", Sheet1.Rows(6), 0)) Then
    lCS = Application.match("Row", Sheet1.Rows(6), 0)
Else
    MsgBox "No any ""Row"" string could be found in """ & Sheet1.Rows(6).Address & """ row..."
    Exit Sub 'the sub is exited here
             'you ca use here (instead of exiting) another Match for a different
             'string or range, if this helps somehow...
End If