none
Excel VBA Do Loop RRS feed

  • Question

  • I have created a do loop which should keep running until a cell is read with a blank, at which point the loop ends. Here is my code. 

    Dim Switch as Variant

    Dim i as interger

    i = 9
    Do While Switch = True
        If IsEmpty(Worksheets("Sheet1").Cells(8, i).Value) = True Then
            Switch = False
        Else
            i = i + 1
            Count = Count + 1
            Switch = True
        End If
    Loop

    Wednesday, July 12, 2017 8:21 PM

All replies

  • Hi,
    You should set True to Switch as an initial value. 
    I've modified and checked your code, and it worked as expected.
    Private Sub btn_SearchEmpty_Click()
      Dim i As Integer: i = 9
      Dim Count As Integer: Count = 0
      Dim Switch As Boolean:  Switch = True
      Do While Switch = True
        If (IsEmpty(Worksheets("Sheet1").Cells(8, i).Value)) Then
            Switch = False
        Else
            i = i + 1
            Count = Count + 1
            Switch = True
        End If
      Loop
      ' ---
      Range("I1").Value = Count
    End Sub
    result after clicking [Search Empty] button:


    Ashidacchi


    • Edited by Ashidacchi Thursday, July 13, 2017 6:23 AM
    Thursday, July 13, 2017 1:05 AM
  • Hi Gradient127,

    Based on your description, your project is VBA or others? Because this is vb.net forum, some issues about VBA is off topic. Please help me to confirm this.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, July 13, 2017 5:17 AM