Excel Macro Tutorial : While Loop and Do While Loop in Excel VBA
In previous Article, we had seen about For Next Loop. In this article we are going to see about While and Do While Loop.
While Loop:
Syntax:
While [condition] [statements] ………… [statements] Wend 
Example: Let’s take the same example what we discussed in previous Article with For..Next Loop.. Calculate the Sum of all Numbers from 1 to 10 Natural Numbers.
iCount=1 sum = 0 While iCount <= 10 sum = sum + i iCount = iCount + 1 Wend
You can see that in While Loop, unlike For loop, we need to increment the Counter variable value by your own. In for loop, you can see, that we need not to mention iCount= iCount + 1 to increment the value of i by 1 in every iterations.
DO...While Loop:
There are two ways of using DO...While Loop. 1. You can put the Condition before getting in to the Loop. 2. You can check the condition at the end of the loop. It means, in this case Loop will be executed once even if the Condition is failing at the first time itself.
Syntax: Type 1
Do While [condition] [statements] [Exit Do] [statements] Loop 
Syntax: Type 2
Do [statements] ............ [statements] Loop While [condition] 
Example: Let's take the same example what we discussed above. Calculate the Sum of all Numbers from 1 to 10 Natural Numbers. We will put the example using While condition at the starting and at the end of the loop.
1. Condition in the starting of the loop
iCount=1 sum = 0 Do While iCount <= 10 sum = sum + iCount iCount = iCount + 1 Loop
1. Condition in the starting of the loop
iCount = 1 sum = 0 Do sum = sum + iCount iCount = iCount + 1 Loop While iCount <= 10
Note:For putting your condition you can use "Until" keyword instead of While See the below Example:
iCount = 1 Sum = 0 Do Sum = Sum + iCount iCount = iCount + 1 Loop Until iCount > 10
But you can see that when you are using the keyword until then you need to change the Condition. For both While or until you can not use the same condition. Because "Until" is like reverse of "While".
To Check out more Excel Macro Tutorials, visit Excel Macro Tutorial 
My name is Vishwamitra Mishra. Friends Call me Vishwa. This blog is authored by me. I am an Excel Geek. Well, this blog talks a lot about my passion in Excels & Macros so I’ll not talk about it :) I am very much passionate about traveling & quite recently discovered that I am a good photographer too..:P

SUBSCRIBEAND GET A FREE!EBOOK FOR EXCEL VBA BEGINNERSDON'T MISS ANY NEW ARTICLE ! 
Get your FREE! EBook & Have Every New Article Delivered Straight To Your EmailBox 
I want to select multiple excel files from a folder and send it to another folder with converting it to CSV format. Please suggest a VBA code for that.
Thanks
I have created this code, everything is ok. Btu this calculation is working up to row 12. But I need to calculate up to blank row. what is the problem here. please help me. where I will use the do while code and what will be the code, please give me the full code.
Sub ScoreMacro()
Dim ws As Worksheet
Set ws = ThisWorkbook.ActiveSheet
Dim Rank As Integer
Dim Clients As Integer
Dim Products As Integer
Dim Margin As Double
Dim RankScore As Integer
Dim ClientScore As Integer
Dim ProductScore As Integer
Dim MarginScore As Integer
Dim CurScore As Integer
Dim TotalScore As Integer
Dim TotalScorePercent As Double
Dim MaxScore As Integer
Dim StartRow As Integer, i As Integer
Dim ColumnBeforeStart As Integer, j As Integer
‘number of columns to be added before Product Rank column
ColumnBeforeStart = 2
StartRow = 7
i = StartRow
j = ColumnBeforeStart + 1
While ws.Cells(i, j) “”
‘ set initial score for current row
TotalScore = 0
‘Get current Rank
If Not (IsNumeric(ws.Cells(i, j))) Then
MsgBox “Rank in row ” & i & ” is not numeric value”
Exit Sub
End If
Rank = ws.Cells(i, j)
‘Process Rank rules
If (Rank >= 0) And (Rank = 101) And (Rank = 501) And (Rank = 1001) And (Rank = 0) And (Clients = 51) And (Clients = 101) And (Client 300) Then
CurScore = 20
End If
‘ Set ClientsScore
ws.Cells(i, j + 3) = CurScore
TotalScore = TotalScore + CurScore
‘Get current Products Value
If Not (IsNumeric(ws.Cells(i, j + 4))) Then
MsgBox “Products value in row ” & i & ” is not numeric value”
Exit Sub
End If
Products = ws.Cells(i, j + 4)
‘Process Products rules
If (Products = 1) And (Products = 3) Then
CurScore = 15
End If
‘ Set ProductsScore
ws.Cells(i, j + 5) = CurScore
TotalScore = TotalScore + CurScore
‘Get current Margin Value
If Not (IsNumeric(ws.Cells(i, j + 6))) Then
MsgBox “Margin value in row ” & i & ” is not numeric value”
Exit Sub
End If
Margin = ws.Cells(i, j + 6)
‘Process Margin rules
If (Margin = 0) Then
CurScore = 30
ElseIf (Margin >= 0.01) And (Margin = 0.11) And (Margin = 0.31) And (Margin = 0.51) Then
CurScore = 25
End If
‘ Set MarginScore
ws.Cells(i, j + 7) = CurScore
TotalScore = TotalScore + CurScore
‘ add total score to current row
ws.Cells(i, j + 8) = TotalScore
‘ I suppose that maxScore is 100 = sum of all max points
‘ in each category
MaxScore = 100
‘ Get TotalScore in percents
TotalScorePercent = TotalScore / MaxScore
‘ add total score to current row
ws.Cells(i, j + 9) = TotalScorePercent
i = i + 1
Wend
End Sub
[…] You also have learnt some basic Programming Lessons as well like If Else Statements, For Loop, While and DO While Loops […]
hi there is Wend the same as next?
and when you make a statement is there a format that you have to use or is it open game?
Hi Ciscocell,
your first question about Wend and Next, yes you can say Wend is like Next only. All the Statements are executed until Next or Wend is not encountered.
The only difference in Next and Wend is that… Wend does not increase any Counter automatically for each loop while "Next" increments the counter by "1" each time by default.
Your second question is not much clear, can you please clarify.
[…] While Loop […]