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.
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