CODEWITHSUNDEEP
excelvbaloops
King
King

Reputation: 451

Loop through a range of cells downwards until blank cell

Here is my for loop attempt:

Sub test()

    Do Until IsEmpty(Worksheets("do not open!").Range("F1"))
        If UI.ComboBoxSource <> ActiveCell Then
            MsgBox "This procedure already exhists. Please click on update summary. "
        Else
        End If
        Worksheets("do not open!").Range("F1").Offset(1, 0).Select
    Loop

End Sub

I am trying to loop through a range of cells (F1 downwards in this case until blank cell), and applying the if statement to compare if the values match.
If the values match, I want the loop to continue till the end.
If there is a mismatch, I want the MsgBox to appear and stop the loop.

This is what I tried to do:

Sub check_procedures()
    Dim ws As Worksheet, rngF As Range
  
    Set ws = Worksheets("do not open!")
    Set rngF = ws.Range("F1")
   
    Do Until rngF.Value = ""
        If UI.ComboBoxSource = rngF.Value Then
            MsgBox "This procedure already exhists. Please click on Update Summary. "
            Exit Sub
        Else
        End If
        
        Set rngF = rngF.Offset(1)
    Loop
    
End Sub

Upvotes: 0

Views: 147

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Please, try the next way. Selecting, activating only consumes Excel resources, without bringing any benefit:

Sub test()
  Dim ws As Worksheet, rngF As Range
  
  Set ws = Worksheets("do not open!")
  Set rngF = ws.Range("F1")
   
   Do Until rngF.Value = ""
         If UI.ComboBoxSource <> rngF.Value Then
            MsgBox "This procedure already exhists. Please click on update summary. "
         Else 
            'do something...
         End If
         Set rngF = rngF.Offset(1)
  Loop

End Sub

But the code looks strange as logic. It will send a lot of messages. What to be done when UI.ComboBoxSource = rngF.Value? Should all cell values in F:F be equal with UI.ComboBoxSource and only by accident one, or two will not be? If only one, you should place Exit Do after, to make the code faster, not iterating up to the first empty cel...

Upvotes: 1

Related Questions