类型不匹配错误VBA循环通过工作表

3 回答

• 0

  当您有一个包含许多列的 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
     For each col in ws.Range("C9:G20").Cells
        if col.Value = "Word" Then
           MsgBox (True)
        end if
     End If
  Next ws
  
  End Sub
  

  回复于 2024-05-19T16:45:20+08:00
• 1

  你无法获得 value 的 ws.Range("C9:G20") 并将其与一个字符串进行比较 . 您已选择多个单元格 . 如果你想在这些单元格中的一个包含"Word"时返回 True ，或者当所有单元格都包含"Word"时，你需要迭代它们 .

  这是一个如何返回您的范围是否至少包含一次“Word”的示例

  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
  

  回复于 2024-05-19T16:45:20+08:00
• 1

  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
  

  回复于 2024-05-19T16:45:20+08:00