How to execute the do while loop in MS Excel?

How to execute the do while loop in MS Excel?

The key in this problem is the TERMINATOR.

You must have the terminator set in your worksheet in order to stop/terminate the loop. Or you can make it by counting. 

In this way you have the reference in your VBA code or in your while loop when to stop the loop.

Here’s one way to set a TERMINATOR in your worksheet. At the end of your data, you must put a unique string which is impossible to find in your actual data. Just an example use the “[EOR]” string as your terminator (EOR – End Of Row]:

Data1
Data2
Data3
[empty]
Data4
Data5
[empty]
Data6
[empty]
Data7
[EOR]  < This will be your terminator. You can put any character in your own taste here. You can use ‘-‘  if you like, or anything that is not look like a mess in your worksheet.
 

• Inside your While Loop test the cell if it is containing the string “[EOR]” then terminate.

If  ActiveCell.Formula=”[EOR] then Exit Loop

One more solution is counting the empty cell. If your data does not contain more than five or ten empty consecutive cells, then this solution is possible.

Here’s how. You must declare a variable that holds the count of spaces as you test the empty cells and a constant variable for the Maximum count of empty cells. Let’s say our Maximum consecutive empty cells are 10.  For each empty cell, increment your counter to one.

If you have encountered not empty cell, reset your counter to zero because your goal is to check if you have encountered the maximum empty cell or in this example is 10.

Here’s how to do it in VBA. Let’s say our variable that holds the counter for empty cells is “SpaceCnt” and for the Maximum consecutive empty cells is “MaxSpace”.

Do While SpaceCnt <= MaxSpace
        If ActiveCell.Formula <> "" Then
            SpaceCnt = 0
        Else
            SpaceCnt = SpaceCnt + 1
        End If
Loop

Now, let’s get into the deeper codes for your project. Test each rows in column “G” as what you have mentioned. Implement the codes discussed above for terminating the While loop. As you test the column “G”, write a value “VALID” at column “F”.

Here’s the complete VBA code:

Using TERMINATOR string at the end of data:

Sub Macro1()
    'we need a variable to increment our row
    'so that we can test the next row
    Dim tRow As Long
    
    'Set the active or selected cell on G1
    'The first row in the test column
    Range("G1").Select
    
    'assign to tRow (Test Row) the current active row number
    'for our future reference in incrementing row
    'as we test each rows
    tRow = ActiveCell.Row
    
    'do the loop while "[EOR]" is not encountered
    'else exit the loop
    'Note:
    'Cell Address is defined as alpha and numeric like "F1"
    '"F" as the column and 1 is the row
    'we need to trick the cell address in Range Function
    'as you can see in our code the parameter for Range is
    'concatenated. We need this because our row is dynamic.
    'in time we need to check the next row, we will increment
    'our variable "tRow".
    Do While Range("F" & tRow).Formula <> "[EOR]"
        'here's the section you want to do with
        'if the cell is not empty then put the
        'value "VALID" at column "G"
        If Range("F" & tRow).Formula <> "" Then
            Range("G" & tRow).Formula = "VALID"
        End If
        'increment the tRow for the next row to be tested.
        tRow = tRow + 1
    Loop
End Sub

Counting consecutive spaces as an artificial intelligent to identify the end of data:

Sub Macro2()
    Dim tRow As Long
    Dim SpaceCnt As Long
    
    'maximum count of spaces
    'in this sample we set it as 10
    Const MaxSpace = 10
    
    tRow = ActiveCell.Row
    
    'initially the SpaceCnt is zero
    SpaceCnt = 0
    
    'end the loop if SpaceCnt reach the MaxSpace.
    Do While SpaceCnt <= MaxSpace
        'Range("B1").Select
        If Range("F" & tRow).Formula <> "" Then
            Range("G" & tRow).Formula = "VALID"
            
            'since we have encountered a non-empty cell
            'reset the SpaceCnt to zero
            SpaceCnt = 0
        Else
            'just incrementing the SpaceCnt
            'SpaceCnt accumulates while the cell is empty
            SpaceCnt = SpaceCnt + 1
        End If
        tRow = tRow + 1
    Loop
End Sub

Additional :  the same process but different in ways of putting values or formula:

Sub Macro3()
    Dim tRow As Long
    Dim SpaceCnt As Long
    
    Const MaxSpace = 10
    
    tRow = ActiveCell.Row
    
    SpaceCnt = 0
    
    Do While SpaceCnt <= MaxSpace
        'put an IF function as a formula on the cell
        'so that if the cell on F is edited or deleted it will display automatically
        'the "VALID" value in G
        Range("G" & tRow).Formula = "=IF(F" & tRow & "<>"""",""VALID"","""")"
        If Range("F" & tRow).Formula <> "" Then
            SpaceCnt = 0
        Else
            SpaceCnt = SpaceCnt + 1
        End If
        tRow = tRow + 1
    Loop
End Sub

Sub Macro4()
    Dim tRow As Long
    
    Range("G1").Select
    tRow = ActiveCell.Row
    
    Do While Range("F" & tRow).Formula <> "[EOR]"
        'put an IF function as a formula on the cell
        'so that if the cell on F is edited or deleted it will display automatically
        'the "VALID" value in G
        Range("G" & tRow).Formula = "=IF(F" & tRow & "<>"""",""VALID"","""")"
        tRow = tRow + 1
    Loop
End Sub

Or, you can simply create a formula on the worksheet without using VBA codes.

Go to column F1 encode this formula:

=IF(F1<>"","VALID","")

Then, copy the cell on the next row up to the end of your data.