Search code examples
excelvba

How to end the loop if the variable is empty


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

I'm trying to find the word description in my range.
If found run the macro and then loop.
If the variable is empty and the variable description is not found end the loop and display the msgbox.

I tried to end the loop using loop until celling is empty.

The variable celling is quoting as empty.


Solution

  • Max, this is worth posting as a new answer to highlight the unintuitive behaviour of FindNext. This works - better candidate for accepted answer than that above. May be a bit pedantic, as in a more elegant solution is possbile:

    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