How to use a FOR Loop in Excel VBA

Aug 13, 2023

How to use a FOR Loop in Excel VBA

Learn how to create a FOR LOOP using the For…Next statement in Microsoft Excel VBA. FOR LOOPS are used to execute code a fixed number of times and can be accessed through the Visual Basic Editor in Excel.

This tutorial demonstrates how to use the Visual Basic Editor in Excel to write a FOR 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.

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

How to display the Developer Tab:
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.03 -> the for next statement allows you to

2.25 -> create a for loop in Excel VBA for loops

5.879 -> are used to execute code a fixed number

8.25 -> of times and can be accessed through the

10.62 -> visual basic editor in Excel let's start

13.71 -> with our first example on sheet 1 we are

16.379 -> going to use the for next statement to

18.6 -> loop through the products in column a

20.279 -> and update the appropriate application

22.199 -> type in column B first we will need to

25.59 -> open the visual basic editor you can do

28.32 -> this using the Alt f11 shortcut or you

31.32 -> can select the Developer tab and then

33.51 -> Visual Basic under the code section if

36.03 -> you don't see the Developer tab we've

38.489 -> left a link in the comments below with

40.649 -> instructions on how to display this tab

42.8 -> here on the Left we have our Excel

45.18 -> objects double click on sheet1 to open

47.789 -> its code window we will create a new

50.219 -> subroutine called T OTN for loop example

54.21 -> one will create an integer variable

56.399 -> called L counter and we'll starter for a

60.03 -> loop by entering for L counter equals to

63.329 -> 2 4 I'll counter is our loop counter

66.54 -> variable and we want the for loop to

68.4 -> start it to end at 4 and increment by 1

71.88 -> each time we pass through the for loop

73.85 -> next we'll use an if statement to test

76.47 -> the value in column a so we enter if

79.14 -> cells l counter comma 1 dot value equals

83.79 -> Excel then

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

88.829 -> first time we enter the for-loop l

91.02 -> counter will be equal to two so it will

93.539 -> test the value in Row two column one

95.909 -> which is cell a2 if this value is equal

99.81 -> to Excel that we want to set the

101.67 -> corresponding value in column B to

104.039 -> spreadsheet so we enter cells L counter

107.909 -> comma 2 dot value equal spreadsheet

110.99 -> let's test for another condition by

113.579 -> entering else if cells l counter comma 1

117.929 -> dot value equals access then and we'll

121.709 -> set the value in column B to database

123.899 -> when this condition is met

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

130.82 -> entering else--if cells l counter comma

133.7 -> one value equals word

137.98 -> and we'll set the value in column B to

140.44 -> word processor for this condition

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

146.73 -> end if and

148.3 -> close up or for next statement with next

150.58 -> L counter when we go back to our

153.73 -> worksheet we'll use a button to run this

155.44 -> code under the Developer tab select

158.2 -> insert and then click on the button icon

161.819 -> left-click and drag to draw your button

164.189 -> on the worksheet select tio TN for loop

167.31 -> example 1 to assign the macro to the

170.01 -> button and then click on OK we'll change

172.89 -> the button text to something more

174.299 -> meaningful

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

178.32 -> and all of the application types in

180.63 -> column B will be updated the for loop

183.42 -> test of the values in cells a2 a3 and a4

186.87 -> and updated the corresponding values in

189.81 -> cells b2 b3 and b4 let's go to sheet 2

194.82 -> and look at another example here we have

197.46 -> a list of participants in column a let's

200.67 -> use for loops to assign each of the

202.65 -> participants to either team a or team be

205.32 -> in the Developer tab click on the visual

208.08 -> basic button again and this time double

211.59 -> click on sheet 2 in the project Explorer

214.34 -> will create a new sub routine called tio

217.26 -> TN for loop example 2 will create two

221.67 -> integer variables called L counter 1 and

224.43 -> L counter 2 and start our first for loop

228.15 -> by using the counter variable called L

230.34 -> counter 1 starting from 2 and ending at

233.46 -> 9 but this time we will use the step

236.07 -> keyword to increment our loop counter by

238.56 -> 2 each pass through the loop

241.569 -> we'll enter cells I'll counter 1 comma 2

245.23 -> dot value equals team a to set the

248.079 -> corresponding value in column B to team

250.269 -> a and then we'll close the for next

252.43 -> statement with next I'll counter 1 we'll

255.7 -> create a second for loop using the

257.44 -> counter variable called L counter 2

259.56 -> starting from 3 and ending at 9 also

264.13 -> using the step keyword to increment by 2

267.03 -> will enter cells L counter 2 comma 2

270.66 -> value equals team B

274.76 -> and we'll close the for next statement

276.98 -> with next L counter - so the first for

280.97 -> loop will update cells b2 b4

283.49 -> b6 + b8 with team a and our second for

288.08 -> loop will update cells b3 b5 b7 and b9 -

292.55 -> team B let's go back to our worksheet

295.31 -> and create another button to run this

297.23 -> code

302.66 -> we'll select T OTN for loop example 2 as

305.93 -> the macro and click on the ok button

308.89 -> again we'll update the button text

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

316.38 -> and the teams will get assigned

318.38 -> alternating between team a and Team B

322.38 -> that covers some basic examples of how

325.11 -> to create a for loop using the for next

327.03 -> statement in VBA if you would like to

330.27 -> see more examples or would like to

332.43 -> download the sample data we used for

334.17 -> this tutorial please visit our website

336.36 -> at tech on the net comm if you found a

339.6 -> tutorial helpful

340.71 -> please leave a like on this video and

342.69 -> don't forget to subscribe to our YouTube

344.67 -> channel for more great Excel tutorials

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