☰

需要 VBA 解决方案 - 清除单元格右侧 7 个单元格的内容

tag-icon • tag-icon microsoft-excel worksheet-function vba macros
需要 VBA 解决方案 - 清除单元格右侧 7 个单元格的内容

我有一份文档，需要在其中查找特定文本并清除其右侧单元格中的所有文本。我不想删除单元格，只想清除它们。原始单元格旁边总共有 7 个单元格。我很难让这个功能适用于多个单元格。任何帮助都非常感谢。

这是我现在的代码：

Sub ClearCellNextToTextValue()
    Dim ws As Worksheet
    Dim cell As Range
    Dim searchRange As Range
    Dim foundCell As Range

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ' Replace "A1:A100" with the range where you want to search for the text value
    Set searchRange = ws.Cells
        
    Set foundCell = searchRange.Find(What:="TOTALS - Current Month", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False)

    If Not foundCell Is Nothing Then
        ' Loop through each found cell
        For Each cell In searchRange
            If cell.Address = foundCell.Address Then
                ' Clear the contents of the cell one column to the right of the found text
                cell.Offset(0, 1).ClearContents
                cell.Offset(0, 2).ClearContents
                cell.Offset(0, 3).ClearContents
                cell.Offset(0, 4).ClearContents
                cell.Offset(0, 5).ClearContents
                cell.Offset(0, 6).ClearContents
                cell.Offset(0, 7).ClearContents
            End If
        Next cell
    End If
End Sub

答案1

请尝试以下操作：

Sub ClearCellNextToTextValue()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range
    Dim firstAddress As String

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Consider setting this to a smaller range
    Set searchRange = ws.Cells

    Set foundCell = searchRange.Find("TOTALS - Current Month")
    
    'store the address of the first find result
    firstAddress = foundCell.Address
    
    Do While Not foundCell Is Nothing
        'Clear the 7 cells to the right
        foundCell.Offset(0, 1).Resize(1, 7).ClearContents
        
        ' Find the next occurrence
        Set foundCell = searchRange.FindNext(foundCell)
        
        ' FindNext loops back to the first found item. If so, we're done...
        If foundCell.Address = firstAddress Then Exit Do
        
    Loop
End Sub

使用“查找”的技巧是您需要使用“查找下一步”来继续搜索第一个结果。

由于 FindNext 会循环回到第一个找到的结果，因此您还必须有一种方法可以在出现这种情况时退出。方法是存储第一个结果的地址，并根据存储的地址检查每个后续找到的单元格。

相关内容