Hide Rows VBA Code of Cells Not Containing Words From Array Mismatch

Question

Basically I have a vba code that runs really well. But when I try to duplicate it to another command button just changing the sheet name and the sub name, it doesn't work and it says Type Mismatch. This is the VBA code that runs properly.

    Sub hide_Rows_by_cell_value()
    Dim wb As Workbook, CompInfo As Worksheet, MufgClient As Worksheet
    Dim srcCl As Range, lr As Long, FltCol As Range, cl As Range, hideRng As Range
    Set wb = ThisWorkbook
    Set CompInfo = wb.Sheets("Company Information")
    Set MufgClient = wb.Sheets("MUFG Client")
    
    Set srcCl = CompInfo.Cells(18, 9)
    arr = Split(srcCl.Value, ",")
    
    lr = MufgClient.Range("AC" & MufgClient.Rows.Count).End(xlUp).Row
    Set FltCol = MufgClient.Range("AC3:AC" & lr) '2nd Row contains table headers
    
    For Each cl In FltCol
        chk = 0
        For i = 0 To UBound(arr)
        chk = chk + InStr(1, cl.Value, Trim(arr(i)), vbTextCompare)
        Next
        If chk = 0 Then
            If hideRng Is Nothing Then
            Set hideRng = cl
            Else
            Set hideRng = Union(hideRng, cl)
            End If
        End If
    Next
    
    hideRng.EntireRow.Hidden = True
    
    End Sub

And this is the VBA code that says type mismatch

  Sub hide_rows_by_cell_value2()
  Dim wb As Workbook, MUFGInfo As Worksheet, LendingFunding As Worksheet
  Dim srcCl As Range, lr As Long, FltCol As Range, cl As Range, hideRng As Range
  Set wb = ThisWorkbook
  Set MUFGInfo = wb.Sheets("MUFG Information")
  Set LendingFunding = wb.Sheets("Lending & Funding")

  Set srcCl = MUFGInfo.Cells(18, 9)
  arr = Split(srcCl.Value, ",")

  lr = LendingFunding.Range("AC" & LendingFunding.Rows.Count).End(xlUp).Row
  Set FltCol = LendingFunding.Range("AC3:AC" & lr) '2nd Row Contains table headers

  For Each cl In FltCol
    chk = 0
    For i = 0 To UBound(arr)
    chk = chk + InStr(1, cl.Value, Trim(arr(i)), vbTextCompare)
    Next
    If chk = 0 Then
        If hideRng Is Nothing Then
        Set hideRng = cl
        Else
        Set hideRng = Union(hideRng, cl)
        End If
    End If

  Next

  hideRng.EntireRow.Hidden = True

  End Sub

The mismatch is in

chk = chk + InStr(1, cl.Value, Trim(arr(i)), vbTextCompare)

I already checked the cell and it is still correct 18,9 (row 18 column i). I also make the range same with the first vba code since the sheet content is a copy paste from the previous sheet

Any help would be greatly appreciated.

Thank you so much in advance.

Answer 1

From the comments it seems you have an error in the range that you're checking (Column "AC")

You could gloss over errors in your check cycle by adding an error check wrapped around the code within the loop:

  For Each cl In FltCol
    If Not IsError(cl) Then
      chk = 0
      For i = 0 To UBound(arr)
        chk = chk + InStr(1, cl.Value, Trim(arr(i)), vbTextCompare)
      Next
      If chk = 0 Then
        If hideRng Is Nothing Then
          Set hideRng = cl
        Else
          Set hideRng = Union(hideRng, cl)
        End If
      End If
    End If

  Next