excel 将命令应用于工作表中的每个表格

czq61nw1  于 2023-03-13  发布在  其他
关注(0)|答案(2)|浏览(159)

此代码选择具有特定颜色的所有单元格。

Sub Validation()
Dim cell As Range
Dim rng As Range
Dim FoundRange As Range
Set rng = Range("L54:L4000")
For Each cell In rng.Cells
    If cell.DisplayFormat.Interior.ColorIndex = 19 Then
        If FoundRange Is Nothing Then
            Set FoundRange = cell
        Else
            Set FoundRange = Union(FoundRange, cell)
        End If
    End If
Next cell
If Not FoundRange Is Nothing Then FoundRange.Select
End sub

它可以在简单的工作表上工作,但我有一个充满表格的工作表,我也需要它来工作。目前它只选择第一个工作表上的单元格。
我试着将它应用到每个表中:

Sub Validation()
Dim co As ListObject
Dim cell As Range
Dim rng As Range
Dim FoundRange As Range
Set rng = Range("L54:L4000")
For Each co In ActiveSheet.ListObjects
    For Each cell In rng.Cells
        If cell.DisplayFormat.Interior.ColorIndex = 19 Then
            If FoundRange Is Nothing Then
                Set FoundRange = cell
            Else
                Set FoundRange = Union(FoundRange, cell)
            End If
        End If
    Next cell
    If Not FoundRange Is Nothing Then FoundRange.Select
Next co
End sub

第一段代码将 * 应用于每个 * 单元格,我想将其视为“整数”并将其应用于每个 * 表。

3df52oht

3df52oht1#

您声明了一个范围rng,它与迭代的listObjects无关...
请尝试下一种方法:

Sub Validation()
 Dim co As ListObject, cell As Range
 Dim rng As Range, FoundRange As Range

 For Each co In ActiveSheet.ListObjects
    Set rng = co.DataBodyRange 'supposing that you want searching in the table dataBodyRange. 
                               'if you need to do it in the whole table (headers included), you should use co.Range
    For Each cell In rng.cells
        If cell.DisplayFormat.Interior.ColorIndex = 19 Then
            If FoundRange Is Nothing Then
                Set FoundRange = cell
            Else
                Set FoundRange = Union(FoundRange, cell)
             End If
        End If
    Next cell
  Next co
  If Not FoundRange Is Nothing Then FoundRange.Select
End Sub
bvjveswy

bvjveswy2#

如果你的意思是表在不同的工作表,那么这段代码将工作。
如果要搜索的是实际的表,则更改rng以查看wrkSht.ListObjects中的每个ListObject,如@FaneDuru给出的答案所示。

Sub Test()

    Dim wrkSht As Worksheet
    Dim rng As Range
    Dim Cell As Range
    Dim FoundRange As Range
    
    For Each wrkSht In ThisWorkbook.Worksheets
    
        wrkSht.Select 'First time I've used Select in about 10 years.
    
        Set FoundRange = Nothing
        Set rng = wrkSht.Range("L54:L4000")
        
        For Each Cell In rng.Cells
            If Cell.Interior.ColorIndex = 19 Then
                If FoundRange Is Nothing Then
                    Set FoundRange = Cell
                Else
                    Set FoundRange = Union(FoundRange, Cell)
                End If
            End If
        Next Cell
        
        If Not FoundRange Is Nothing Then
            FoundRange.Select 'Second time.....
        End If
        
    Next wrkSht
    
End Sub

相关问题