How to execute the do while loop in MS Excel?

Asked By 290 points N/A Posted on -
qa-featured

Hi,

I am working on a project in Excel/VBA and am unfortunately got stuck in a technical issue.

In fact, I am trying to develop a loop as I want to check for a cell and on the other hand the cell contains nothing, I want to populate some data into another particular cell.

If I would tell you in context then, it would be as like I want to check cell f1, if it is not empty then I would like to enter the VALID in a specific cell (suppose) G1. But when f1 should be blank/empty then the G1 would likely leave blank.

Conclusion: at the point/stage F1 appears as empty cell, that should be or possibly the end of my produced data. I want to do this via do while loop to stop (the operation) but I am not been able to do so.

To fix this issue, I tried using do while loop but because of my sketchy on the syntax, I failed to do so. I also have prepared an (if) statement to perform the same operation in Excel but the project requirements consists of performing it in while loop to stop formula/command therefore, no benefit has been gained.

I am very worried about this and I want to learn this trick as early as possible.

Therefore, I want to learn/know: How to execute the do while loop in MS Excel?

I’ll greatly thankful to any help.

SHARE
Best Answer by bertdesk
Best Answer
Best Answer
Answered By 0 points N/A #99505

How to execute the do while loop in MS Excel?

qa-featured

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.

Answered By 0 points N/A #99506

How to execute the do while loop in MS Excel?

qa-featured

Open Excel, Select the cell in excel and then pressing Alt key + F11 to Open VBA Editor.

Open up Sheet1 then open the drop down showing (General) option and (worksheet) option. Go or select for "Worksheet" option here. Secondly, from (declarations) drop down here we select for the function, Go or select for "Activate" function here.

Now we can type for the code in between here like :

Private Sub Worksheet Activate()

Dim ws As Worksheet

Dim row As Integer

Dim col As Integer

row = 1

col = 1

Set ws = ActiveWorkboook.Sheets("Sheet1" )

Do

          ws.Cells(row, col).Value = "Hello!"

          row = row + 1

          col = col + 1

Loop While row < 5 And col < 5

End Sub

Now Close the VBA. Now we can check the working of the above code by just switching to Sheet2 and then back to Sheet1, now we can see the Output of Do-while will appear here. It will show four times "Hello!" print in each row and column incremented as per code we have given.

Related Questions