How to use the IF-THEN-ELSE statement in Excel VBA

Aug 13, 2023

How to use the IF-THEN-ELSE statement in Excel VBA

Learn how to use the IF-THEN-ELSE statement in Microsoft Excel VBA. This tutorial demonstrates how to use the Visual Basic Editor in Excel to write an IF-THEN-ELSE statement. We provide 2 easy to follow examples and take you step-by-step through the process of creating the VBA code and running it.

IF-THEN-ELSE statement 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 if-then-else statement is available

2.31 -> in Excel through the visual basic editor

4.319 -> or macros you can use the if-then-else

8.37 -> statement to execute one set of code

10.44 -> when a condition is true or another set

12.99 -> of code if it is false this is done

15.75 -> through VBA code instead of a worksheet

17.91 -> function let's start with a simple

20.52 -> example on sheet 1 we are going to use

23.609 -> the if then else statement update cell

25.949 -> c2 with north south east or west

28.76 -> depending on the region code entered in

31.05 -> cell a2 so if we entered n in cell a2 we

35.46 -> want North to appear in cell c2 to open

38.85 -> the visual basic editor he can do this

41.07 -> using the Alt f11 shortcut or you can

44.82 -> select the Developer tab and then Visual

47.67 -> Basic under the code section if you

51.149 -> don't see the Developer tab in your

52.8 -> version of Excel we've left a link in

55.11 -> the comments below with instructions on

56.879 -> how to display this tab here on the Left

60.3 -> we have our Excel objects to make it

62.969 -> easier we are going to create our VBA

65.22 -> code under the sheet 1 object so double

68.46 -> click on sheet1 to open its code window

70.79 -> will create a new subroutine called t OT

73.979 -> n if example 1 will create a string

77.61 -> variable called L region and the second

81.299 -> string variable called L region name

83.49 -> these variables will be used in our code

86.13 -> below we'll set the L region variable to

89.28 -> the value stored in cell a2

93.54 -> and we'll start our if-then-else

95.7 -> statement by typing if l region equals n

99.3 -> then when this condition is met we'll

102.99 -> set the variable called L region name to

105.21 -> north since he wanted to test another

108.18 -> condition will enter else if L region

111.18 -> equals s then

113.92 -> notice that there is no space between

116.17 -> else and if it is just one word when l

120.4 -> region is s we'll set the variable to

122.59 -> south let's add another condition by

125.32 -> entering else if L region equals e then

129.069 -> and we'll set the L region name variable

132.01 -> to east when this condition is met and

135.09 -> finally we want to handle the case where

137.29 -> L region is equal to W we can either

140.62 -> enter another else--if condition or we

143.17 -> can use an else statement just to show

145.93 -> you the syntax we'll use an else

147.489 -> statement to handle this condition so

150.28 -> we'll enter else L region name equals

154.12 -> West

156.29 -> and then we'll close off our

157.849 -> if-then-else statement with ENDIF

161.569 -> now let's set cell c2 with the value in

164.719 -> the L region named variable

168.5 -> now when we go back to our worksheet we

171.11 -> need some way to run this code so let's

173.45 -> create a button on the developers tab

176.12 -> under insert click on the button icon

181.069 -> and then left-click and drag to draw

183.53 -> your button on the worksheet select t OT

187.01 -> n if example 1 to assign the macro to

189.769 -> the button and then click on OK we'll

192.469 -> change the button text to something more

194.239 -> meaningful now when you set a value in

197.45 -> cell a2 and then click on the button our

199.909 -> VBA code will update the value in cell

202.67 -> c2 for a value of n it will set cell c2

206.359 -> to North for a value of s it will set it

209.329 -> to South and so on

214.61 -> now let's go to sheet 2 and look at

216.8 -> another example here we have a list of

219.11 -> students in column a and their

221.03 -> corresponding grade and column B let's

223.7 -> update a comment and column C based on

226.4 -> the grade in column B in the developer

229.13 -> tab click on the visual basic button

231.17 -> again and this time double click on

233.75 -> sheet 2 in the project Explorer will

236.54 -> create a new sub routine called t OT n

239 -> if example 2 and will create a for each

242.48 -> loop testing each grade in the range b2

245.39 -> to b8

247.69 -> we'll starter if-then statement by

249.85 -> entering if grade equals a and we'll use

252.94 -> the or keyword here and enter or grade

255.73 -> equals B in this case the or keyword

258.91 -> will test if either the grade is an A or

260.83 -> a B now we want to update the comment

263.92 -> value which is found in column C so

266.95 -> we'll use offset to find the cell since

269.86 -> we are currently referencing a cell in

271.78 -> column B to find the column C counter

274.78 -> part we need to offset by 0 rows and 1

277.93 -> column so we'll enter 0 comma 1 0 is a

282.43 -> number of rows and 1 is a number of

284.71 -> columns to shift over and we'll set the

288.07 -> value in the cell to great work

292.439 -> now we will enter else--if great equals

295.709 -> C then and we'll set the corresponding

299.55 -> value in column C to needs improvement

307.82 -> and we'll finish off with else and set

310.7 -> the column C cell to time for a tutor

316.79 -> and then we'll close off our

318.4 -> if-then-else statement with end if

321.31 -> finally we'll close off our for each

323.78 -> loop by entering next grade let's go

327.17 -> back to our worksheet and create another

328.82 -> button to run this code

336.879 -> we'll select sheet 2 t OT n if example 2

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

344.429 -> again we'll update the text

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

351.78 -> and all of the comments in column C will

354.03 -> get updated for a grade of a or B the

356.94 -> comment will show great work for a grade

359.31 -> of C the comment will show needs

361.17 -> improvement and for all other grades

363.51 -> we'll see time for a tutor as the

365.91 -> comment this covers some basic examples

369.03 -> of the if-then-else statement in Excel

372 -> VBA

372.63 -> if you would like to see more examples

374.91 -> or would like to download the sample

376.86 -> data we use for this tutorial please

379.26 -> visit our website at tech on the Netcom

381.68 -> if you found our tutorial helpful please

384.42 -> leave a like on this video and don't

386.85 -> forget to subscribe to our youtube

388.41 -> channel for more great Excel tutorials

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