时间戳

首页 > 解决方案 > VBA Do（删除查找为真的列）直到查找为假然后退出执行

vba - VBA Do（删除查找为真的列）直到查找为假然后退出执行

问题描述

我有一个 excel 文件，其中包含文本“来自 Trans 的收入”，我需要在其中删除整个列。

这是我在 VBA 2010 中的当前代码，它一直有效，直到不再有“来自 Trans 的收入”的单元格为止；我无法让它跳出循环。

知道为什么吗？

Dim rng1 As Range
Dim target1 As String

target1 = "Income From    Trans"
Set rng1 = Cells.Find(What:=target1, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Do Until rng1 Is Nothing
    Cells.Find(What:=target1, After:=ActiveCell, _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, 
SearchDirection _
         :=xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.EntireColumn.Delete
Loop

标签： vbaloops

解决方案

也检查循环内的搜索结果

Dim rng1 As Range, target1 As String, firstFound As String

target1 = "Income From    Trans"

Set rng1 = Cells.Find(What:=target1, After:=ActiveCell, LookIn:=xlFormulas, _
                      LookAt:=xlPart, SearchOrder:=xlByRows, _
                      SearchDirection:=xlNext, MatchCase:=False)

If Not rng1 Is Nothing Then

    Do

        firstFound = rng1.Address

        Set rng1 = Cells.Find(What:=target1, After:=ActiveCell, LookIn:=xlValues, _
                              LookAt:=xlPart, SearchOrder:=xlByRows, _
                              SearchDirection:=xlNext, MatchCase:=False, _
                              SearchFormat:=False)

        If Not rng1 Is Nothing Then rng1.EntireColumn.Delete

    Loop While Not rng1 Is Nothing And rng1.Address <> firstFound

End If

推荐阅读