How to end the loop is the variable is empty

Question

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

Hi im trying to find the word description in my range, if description is foudn then it should run the macro and then loop. but if the variable is empty and the variable description is not found i want the loop to end and display the msgbox. I have tried to end the loop using loop until the celling is empty but it doesnt seem to work. The variable celling is quoting as empty so im unsure why this is not working. Any help would be greatly appreicated thanks max

score 1 · Accepted Answer · answered Sep 14 '20 at 00:24

1

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

answered Sep 14 '20 at 00:24

klausnrooster

520
3
13

Call source is a different sub i run to move the data into a new line ready to transfer into my master data sheet. The code simplified basically moves the data into a new line and then deletes the range. I think this is where i keep getting my error, as when it loops i want it to then look for the new description value after the data has been deleted. so for example when i run this code it finds it perfectly at G11, but then in thoery the data should then be deleted and then loop back to the start the where it then looks for the next description, which when deleted would be G12. – Max Murrell Sep 14 '20 at 05:29
Because the range varies in size i need to find the next description after its been deleted and if it cant find the description it then need to break the loop and find the next blank – Max Murrell Sep 14 '20 at 05:31
If you have any advice about how i can achieve this would be great thank you. – Max Murrell Sep 14 '20 at 05:31
Be careful about deleting cells, rows, or columns. Many others may be moved to 'fill the gap', and all their addresses change. Loops will then skip over some unless you start at the last cell/row/column and work your way backwards (typically up and/or to the left). I suspect there is a good approach that gives the desired result that is relatively simpler, but may mean you have to scrap part or all your code so far. Screenshots from Excel showing the start, end result, and maybe a few intermediate states would really communicate the goal. Replace private text/nums with A / 1 first. – klausnrooster Sep 15 '20 at 03:41

klausnrooster · Answer 2 · 2020-09-13T21:49:22.730

0

'Max, guessing a little at your intent - May need your help there. Does this get you closer? I don't think I can do better on a GNU/Linux box.

Sub max()

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

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

Dim celladdress 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
        MsgBox celladdress
    'Else: Call Source   'What is Source? Not this sub, is it?
    
    End If
    Set celling = range.FindNext 
Loop Until celling Is Nothing

MsgBox "done"

End Sub

edited Sep 13 '20 at 21:49

answered Sep 13 '20 at 20:48

klausnrooster

520
3
13

Hi Klausnrooster, Thank you for your response. Yes that is very close. The difficulty im having is when its run through the loop and sorted the data into one lines its leaves one set of data where there is not description at the botttom. I left the msgbox "done" so i know where to add the code when the loop breaks. I have tried your peice but its so close to working. – Max Murrell Sep 13 '20 at 20:57
I'm trying to figure out if celling is blank/empty/nothing then can i break the loop and then add my additional code into the bottom. if you could help me with this it would be greatly appreicated thanks max – Max Murrell Sep 13 '20 at 20:58
Max, I rushed. Sorry. I was fixing my bonehead error and hit a key combo that turned my only Windows PC off. Now it won't run. I'll see if I can fix my error on this GNU/Linux box. Hang on... – klausnrooster Sep 13 '20 at 21:30
Thank you klausnrooster your help is very much appreicated – Max Murrell Sep 13 '20 at 22:02
i have tried with the with block, but im unsure about whether im using it in the correct way. if you have some free time tomorrow would be able to help me with it please? – Max Murrell Sep 13 '20 at 22:21
After 5 PM Eastern Std Time I'll check back here. Post your latest code as an answer here, if the question hasn't been closed. Please describe what "Call Source" means. If it means "Start this Sub over again", that's a significant detail. Cheers! – klausnrooster Sep 13 '20 at 22:58
Win10 box finally booted, and I have an improved answer, but it still might be good to know what "Call Source" does. – klausnrooster Sep 14 '20 at 00:26

How to end the loop is the variable is empty

2 Answers2