How to use a WHILE Loop in Excel VBA

Aug 13, 2023

How to use a WHILE Loop in Excel VBA

Learn how to create a WHILE LOOP using the While…Wend statement in Microsoft Excel VBA. WHILE LOOPS are used when you are not sure how many times you want to execute the VBA code within the loop body.

This tutorial demonstrates how to use the Visual Basic Editor in Excel to write a WHILE Loop. We provide 2 easy to follow examples and take you step-by-step through the process of creating the VBA code and running it.

WHILE Loop in VBA:
https://www.techonthenet.com/excel/fo…

How to display the Developer Tab in the toolbar:
https://www.techonthenet.com/excel/qu…

Excel Functions/Formulas:
https://www.techonthenet.com/excel/fo…

Excel Tutorials:
https://www.techonthenet.com/excel/in…

Don’t forget to subscribe to our channel for more great Excel tutorials!

Visit us at: https://www.techonthenet.com
or follow us on:

Facebook: https://www.facebook.com/techonthenetcom
Twitter: https://twitter.com/tech_on_the_net
Pinterest: https://www.pinterest.com/techonthene…

Content

0.16 -> the while when statement allows you to

2.24 -> create a while loop in excel vba

5.68 -> while loops are used when you are not

7.359 -> sure how many times you want to execute

9.44 -> the vba code

10.639 -> within the loop body in excel let's

13.599 -> start with our first example on sheet1

16.16 -> we are going to use the wild when

17.84 -> statement to loop through the products

19.52 -> in column a

20.64 -> and update the appropriate application

22.56 -> type in column b

24.64 -> first we will need to open the visual

26.48 -> basic editor

28 -> you can do this by using the alt f11

30.48 -> shortcut

32 -> or you can select the developer tab and

34.32 -> then visual basic under the code section

37.52 -> if you don't see the developer tab we've

39.52 -> left a link in the comments below with

41.44 -> instructions on how to display it

43.6 -> here on the left we have the project

45.6 -> explorer which displays our excel

47.92 -> objects double click on sheet1 to open

50.8 -> its code window

52.559 -> we will create a new subroutine called

54.64 -> totn

55.68 -> while loop example 1.

59.28 -> we'll create an integer variable called

61.28 -> lsearchrow

63.28 -> we will set the alsurtrow variable to 2

66.159 -> since we want to start on the second row

68.159 -> in column a

70.479 -> in this example we want our while loop

72.56 -> to test each value in column a

75.04 -> starting with the second row and if

77.04 -> there is a value

78.24 -> we want to execute our while loop once

81.28 -> the first cell in column a with no value

83.84 -> is found

84.479 -> we want the while loop to terminate so

87.28 -> we'll enter the following to start our

89.04 -> while loop

90.32 -> and then we will enter wend to end our

92.56 -> loop

93.68 -> since we don't want to put ourselves in

95.28 -> an infinite loop we will increment our

97.28 -> row counter

98.32 -> by entering l search row equals l search

101.28 -> row plus one

102.96 -> now we need to write the code inside of

104.799 -> our while loop

106.32 -> we'll use an if statement to test the

108.159 -> value in column a

109.68 -> so we enter if cells l search row comma

113.2 -> 1

113.92 -> dot value equals excel then

117.6 -> this will test the value in column a the

120.32 -> first time we enter the while loop

122 -> l search row will be equal to 2 so we'll

124.479 -> test the value in row 2

126.24 -> column 1 which is cell a2

129.92 -> if this value is equal to excel then we

132.239 -> want to set the corresponding value in

134.319 -> column b

135.28 -> to spreadsheet so we enter cells l

138.48 -> search row comma two dot value equals

141.2 -> spreadsheet

143.599 -> let's test for another condition by

145.44 -> entering else if cells

147.68 -> l search row comma 1 dot value equals

150.879 -> access

152.16 -> then and we'll set the value in column b

155.28 -> to database

156.08 -> when this condition is met

160 -> and we'll test for one more condition by

161.92 -> entering else if cells

163.76 -> l search row comma 1 dot value equals

166.64 -> word

169.04 -> and we'll set the value in column b to

171.12 -> word processor for this condition

178 -> we'll close off our if statement with

179.76 -> end if

181.519 -> when we go back to our worksheet we will

183.36 -> use a button to run this code

185.76 -> under the developer tab select insert

188.319 -> and then click on the button icon

192.08 -> left click and drag to draw your button

194.08 -> on the worksheet

195.36 -> select totn while loop example 1 to

198.159 -> assign the macro to the button

199.68 -> and then click on ok we'll change the

202.8 -> button text to something more meaningful

210.56 -> to run the code just click on the button

212.799 -> and all of the application types in

214.64 -> column b will be updated

217.28 -> the while loop tested the values in cell

219.36 -> a2 a3

220.879 -> and a4 and updated the corresponding

223.44 -> value in cells b2

225.36 -> b3 and b4 once the while loop

228.72 -> encountered the value in cell a5

231.12 -> the loop is terminated since cell a5 has

233.76 -> no value

235.84 -> let's go to sheet 2 and look at another

238 -> example

239.519 -> here we have a list of first names in

241.439 -> column a

242.799 -> let's use a while loop to change the

244.64 -> fill color and font color of the cells

246.879 -> that have values

249.2 -> in the developer tab click on the visual

251.12 -> basic button again

254.08 -> and this time double click on sheet 2 in

256.56 -> the project explorer

258.72 -> we'll create a new subroutine called

260.56 -> totn while loop

262.16 -> example 2. again we'll create an integer

265.6 -> variable called

266.56 -> l search row

272.32 -> and we'll create the same while loop as

274.4 -> the previous example

275.84 -> so that we test each value in column a

278.639 -> starting with cell

279.6 -> a2 and terminate the loop only when a

282.32 -> cell does not contain a value

285.28 -> we will again increment our l search row

287.52 -> variable

288.32 -> so that we don't force ourselves into an

290.24 -> infinite loop

292.72 -> now our while loop is set up and we want

294.56 -> to enter the code that will be

296 -> executed within the loop first we want

299.36 -> to set the fill color to blue

301.759 -> although there are some built-in

303.199 -> constants such as bb

304.96 -> blue and vb white we want to choose a

307.6 -> custom color

309.36 -> so let's go back to our spreadsheet and

311.199 -> choose our color to see what the color

313.12 -> value is

314.639 -> let's select cell d1 and choose our fill

317.28 -> color

318.4 -> click on the home tab and then the fill

320.4 -> option

321.759 -> we will choose this last dark blue now

324.96 -> go back to the vba

326.32 -> editor and in the immediate window type

328.639 -> either question mark or print

330.8 -> and then range d1 dot interior dot color

334.56 -> and press

335.12 -> enter the color number for this dark

337.84 -> blue is

338.56 -> six five six seven seven one two

342.08 -> now set the interior color to this

343.919 -> number

351.28 -> and let's set the font color to vb white

353.44 -> to make it easier

355.52 -> vb white is one of our constants that we

357.68 -> can use in vba

360.88 -> let's go back to our worksheet and

362.479 -> create another button to run this code

365.039 -> but first we'll remove the color from

367.199 -> cell d1

372.96 -> we'll select sheet2.totn while loop

376.4 -> example 2

377.36 -> as the macro and click on the ok button

380.639 -> again we'll update the button text

384.479 -> to run the code just click on the button

386.72 -> and starting at row 2

388.24 -> all of the cells in column a will be

390.319 -> formatted with a dark blue background

392.4 -> and white font

394.479 -> that covers some basic examples of how

396.56 -> to create a while loop using the while

398.639 -> when statement in vba

401.12 -> if you would like to see more examples

403.199 -> or would like to download the sample

404.72 -> data we used for this tutorial

406.56 -> please visit our website at

408.12 -> techonthenet.com

410.479 -> if you have found our tutorial helpful

412.479 -> please leave a like on this video

414.16 -> and don't forget to subscribe to our

415.759 -> youtube channel for more great excel

418.199 -> tutorials

Source: https://www.youtube.com/watch?v=j8_f4WK5t70