Type mismatch error VBA loop through worksheets

Question

I keep getting a type mismatch error and have tried changing the type a few times. I'm just trying to loop through each worksheet and a specified range to see if that word exists in every cell of that range.

Sub CheckWord()


Dim arrVar As Variant
Dim ws As Worksheet
Dim strCheck As Range

Set arrVar = ActiveWorkbook.Worksheets
'MsgBox (arrVar)

For Each ws In arrVar
   If ws.Range("C9:G20").Value = "Word" Then
    MsgBox (True)
   End If
Next ws

End Sub

Answer 1

You can't get the value of ws.Range("C9:G20") and compare it to one string. You've selected multiple cells. If you want to return True when nay one of these cells contains "Word" or when all of them contain "Word" you'll need to iterate over them.

This is an example of how to return whether or not your range contains "Word" anywhere at least once

Function CheckWord()
    Dim arrVar As Variant
    Dim ws As Worksheet

    Set arrVar = ActiveWorkbook.Worksheets

    For Each ws In arrVar
       Dim c
       For Each c In ws.Range("C9:G20").Cells
            If c = "Word" Then
                CheckWord = True
                Exit Function
            End If
       Next c
    Next ws
End Function

Answer 2

When you have a range with many columns, it creates an array. Taking the array into consideration like so:

Sub CheckWord()


Dim arrVar As Variant
Dim ws As Worksheet
Dim strCheck As Range

Set arrVar = ActiveWorkbook.Worksheets
'MsgBox (arrVar)

For Each ws In arrVar
   For each col in ws.Range("C9:G20").Cells
      if col.Value = "Word" Then
         MsgBox (True)
      end if
   End If
Next ws

End Sub

Answer 3

Sub CheckWord()
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        If Not ws.Range("C9:G20").Find(What:="Word", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) Is Nothing Then MsgBox "Found in " & ws.Name
    Next ws
End Sub