How to execute the do while loop in MS Excel?

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?

How to execute the do while loop in MS Excel?

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

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

'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

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.

How to execute the do while loop in MS Excel?

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.

Techyv is one of the leading solution providers covering different aspects of Computers and Information Technology. We have a hardworking team of professionals in different areas that can provide you with guaranteed solutions to a blend of your problems. We have a dedicated and devoted team of professional writers with multi-dimensional experience of several years. As a result, we produce quality content on a variety of subjects.