VBA代码故障：如何在Excel列表列中查找含'Rebill'的行

Let's break down why your current code isn't working, then walk through two solid solutions—one that fixes your original loop approach, and a more efficient method using AutoFilter for larger datasets.

What's Wrong With Your Current Code? #

Your snippet has a few critical issues that prevent it from finding "Rebill" and updating the correct rows:

• Incorrect Cell Reference: tbl.ListColumns(TagetColumnLabel_1).DataBodyRange.Column returns the column number (e.g., 5 for column E), not the value of the current row's cell. You need to target the specific cell in each iteration.
• Typos: TagetColumnLabel_1 is missing an "r" (should be TargetColumnLabel_1). This could cause variable resolution errors.
• Vague InStr Check: While InStr returns a non-zero number when a match is found, explicitly checking > 0 makes your logic clearer and avoids edge cases.
• Whole Column Assignment: tbl.ListColumns(TagetColumnLabel_2).DataBodyRange.FormulaR1C1 sets the formula for every row in the column, not just the matching row.

Solution 1: Corrected Loop Approach #

This fixes your original logic to target individual rows correctly:

Sub FindRebill_Loop()
    Dim tbl As ListObject
    Dim TargetColumnLabel_1 As String
    Dim TargetColumnLabel_2 As String
    Dim i As Long
    
    ' Initialize your variables (adjust these to match your workbook/table)
    Set tbl = ThisWorkbook.Worksheets("DATA").ListObjects("YourTableName") ' Replace with your table name
    TargetColumnLabel_1 = "Contents Total"
    TargetColumnLabel_2 = "YourTargetColumnName" ' Replace with the column you want to update
    
    ' Make sure the table has data rows to process
    If Not tbl.DataBodyRange Is Nothing Then
        For i = 1 To tbl.ListColumns(TargetColumnLabel_1).DataBodyRange.Rows.Count
            ' Check if the current cell contains "Rebill" (case-insensitive)
            If InStr(1, tbl.ListColumns(TargetColumnLabel_1).DataBodyRange(i, 1).Value, "Rebill", vbTextCompare) > 0 Then
                ' Assign the formula to ONLY the matching row's target column
                tbl.ListColumns(TargetColumnLabel_2).DataBodyRange(i, 1).FormulaR1C1 = "=tb_DATA[[#This Row],[Service/Log Formula]]"
            End If
        Next i
    End If
End Sub

Solution 2: Efficient AutoFilter Method (Best for Large Tables) #

Looping through every row can be slow for big datasets. Using AutoFilter to isolate matching rows and batch-update them is much faster:

Sub FindRebill_AutoFilter()
    Dim tbl As ListObject
    Dim TargetColumnLabel_1 As String
    Dim TargetColumnLabel_2 As String
    Dim filteredRange As Range
    
    ' Initialize variables
    Set tbl = ThisWorkbook.Worksheets("DATA").ListObjects("YourTableName")
    TargetColumnLabel_1 = "Contents Total"
    TargetColumnLabel_2 = "YourTargetColumnName"
    
    ' Clear any existing filters first
    If tbl.AutoFilter.FilterMode Then tbl.AutoFilter.ShowAllData
    
    ' Apply filter to find rows containing "Rebill" (wildcards match any text before/after)
    tbl.ListColumns(TargetColumnLabel_1).Range.AutoFilter _
        Field:=1, _
        Criteria1:="*Rebill*", _
        Operator:=xlAnd
    
    ' Get the visible rows in the target column (skip the header)
    On Error Resume Next ' Handle case where no matches are found
    Set filteredRange = tbl.ListColumns(TargetColumnLabel_2).DataBodyRange.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    ' Batch-update all matching rows at once
    If Not filteredRange Is Nothing Then
        filteredRange.FormulaR1C1 = "=tb_DATA[[#This Row],[Service/Log Formula]]"
    End If
    
    ' Optional: Remove the filter to show all rows again
    tbl.AutoFilter.ShowAllData
End Sub

Key Notes #

• Case Sensitivity: Use vbTextCompare (as in the loop example) for case-insensitive searches. Omit it (default is vbBinaryCompare) if you need exact case matching.
• Table Name Flexibility: If tb_DATA is the same table you're working with, replace it with tbl.Name to make the code dynamic (e.g., ="=" & tbl.Name & "[[#This Row],[Service/Log Formula]]").
• Error Handling: The On Error Resume Next in the AutoFilter method prevents crashes if no rows match "Rebill".

内容的提问来源于stack exchange，提问作者Adéla Škrábalová