Excel VBA Select Case Statement - Two Examples

In this video, we explain the Select Case statement of Excel VBA with two examples.

Select Case offers a neater and more concise alternative to other logical statements such as the IF statement.

You need only refer to the test expression once and lists the case conditions and resulting actions. This ensures a more condensed structure than nested IF's.

In this video we see the Select Case structure used to test a range of numbers (2 examples given for this) and then to test a list of string values).

Various ways of writing the Select Case statement are given with the reasons why explained.

Find more great free tutorials at;

*** Online Excel Courses ***

Connect with us!

[joefromdc]

Last comment, once i understood how to read the case it made sense.
Look at the values in column 'B'/ (rownum, 2) if they are = to xyz. Display the answer in column 'C' (rownum, 3)
Keep doing this until column 'A'/ (rownum, 1) is blank

[tannertucker]

This is by far one of the best explanations that I have viewed. Is there a way to evaluate the Location with out typing the whole location string in the VBA code? For example, instead of "London", type "Lon*". I have only unsuccessfully played around with Case Like. Thank you very much.

[tannertucker]

Always excellent. Do you have any advice on updating code when inserting adding and deleting columns? Is there a way to make the cell / range references dynamic in the code? Thank you

[Vando.Santos]

Is there a possibility of a calculus instead of return "Cell(RowNum, 1).Value = "E"?

[joefromdc]

Gd video, my issue was determining how it knew to start on column 3/C . Rownum = 2, i assume meant start on row 2

[joefromdc]

Okay think i got it
Rownum = 2, is the row to start on
, 3) means start on the 3rd column
Wish u would hv explained that a little better

[rdcycles]

Hi, I appreciate this video is quite old now but hoping you might see this and might be able to help.

I have a sub similar to your second example. However I want the list of town in the case is to be variable. I.e it will change on the next pass of the code. I am defining the string earlier in the code.

So if I liken it to the video example, I’d want the first case is = string1. Where string1 = “London”, “Brighton”, “Southampton”, but the next time through the select case it might be that string1 = “Leeds”, “Liverpool”

I’m struggle to make the “case is = string1” work.

Is it even possible?

TIA

[nareshsharma]

Hi Sir, I hope you are very well, pls try to understand, I want to outlook mail move in separate folder in d drive (date wise folder like - march-1 & March-2 ....) If you can help

I m doing manually copy & ...