时间戳

首页 > 解决方案 > 如何结束循环是变量为空

excel - 如何结束循环是变量为空

问题描述

Sub max()

Sheets(1).Select
Sheets(1).Name = "Sheet1"

Dim rng As Range
Dim celladdress As String
Dim celling As Variant

Do Until IsEmpty(celling)

    If celling > "G4" Then
    
    Set rng = Range("G3:G1000").Find(what:="Description")
    rng.Find what:="Description"
    
    celladdress = rng.Address(-1)
    celling = celladdress
    Else: Call Source

    End If
Loop

MsgBox "done"

End Sub

嗨我试图在我的范围内找到单词描述，如果描述是 foudn 那么它应该运行宏然后循环。但如果变量为空且未找到变量描述，我希望循环结束并显示 msgbox。我试图使用循环结束循环，直到单元格为空，但它似乎不起作用。变量 celling 引用为空，所以我不确定为什么这不起作用。任何帮助将不胜感激，感谢max

标签： excelvbavba7vba6

解决方案

Max，这是值得发布的一个新答案，以突出 FindNext 的不直观行为。这有效 - 比上述更好的接受答案候选人。可能有点迂腐，因为可能有一个更优雅的解决方案：

Sub max()

Sheets(1).Select
Sheets(1).Name = "Sheet1"

Dim rng As Range
Set rng = Range("G3:G1000")

Dim celladdress As String
Dim celladdressPrevious As String
Dim celling As Range
 
Set celling = rng.Find(what:="Description")
If celling Is Nothing Then
    MsgBox "Not found, exiting"
    Exit Sub
End If

Do
    'Set celling = range.FindNext    'Keeps returning first range found! Maybe "With" block on rng will work.
    If celling.Row > 4 Then
        'celling.Activate
        celladdress = celling.Offset(-1, 0).Address
        If celladdress = celladdressPrevious Then GoTo WereDone
        celladdressPrevious = celladdress
        MsgBox celladdress
    'Else: Call Source   'What is Source? Not this sub, is it?
    
    End If
    If celling.Row = 1000 Then Exit Sub
    Set rng = Range("G" & celling.Row & ":G1000")
    Set celling = rng.Find(what:="Description")
Loop Until celling Is Nothing

WereDone:
MsgBox "done"

End Sub

推荐阅读