How to Use "If.. Then" or "Select.. Case" in Excel Visual Basic

Step-by-Step Guide

1. 1

   Step 1: You can use two statements in Microsoft Visual Basic for Applications to perform a conditional test: an If...Then...Else statement or a Select Case statement.

   Sample Macro Using If...Then...Else Statement Sub Using_IF() Dimension the variable.
   
   Dim x As Integer ' Place a value in x. x = Int(Rnd * 2000) ' Test to see if x less than or equal to
   10.
   
   If x <= 10 Then ' Display a message box.
   
   MsgBox "X is <=10" ' Test to see if x less than or equal to 100 and greater than
   10.
   
   ElseIf x <= 100 And x > 10 Then MsgBox "X is <=100 and > 10" ' Test to see if x less than or equal to 1000 and greater than
   100.
   
   ElseIf x <= 1000 And x > 100 Then MsgBox "X is <=1000 and > 100" ' Test to see if x less than or equal to 2000 and greater than
   1000.
   
   ElseIf x <= 2000 And x > 1000 Then MsgBox "X is <= 2000 and > 1000" If none of the above tests returned true.
   
   Else MsgBox "X does not fall within the range" End If End Sub Sample Macro Using Select Case Statement Sub Using_Case() ' Dimension the variable.
   
   Dim x As Integer ' Place a value in x. x = Int(Rnd * 2000) ' Start the Select Case structure.
   
   Select Case x ' Test to see if x less than or equal to
   10.
   
   Case Is <= 10 ' Display a message box.
   
   MsgBox "X is <=10" ' Test to see if x less than or equal to 100 and greater than
   10.
   
   Case 11 To 100 MsgBox "X is <=100 and > 10" ' Test to see if x less than or equal to 1000 and greater than
   100.
   
   Case 101 To 1000 MsgBox "X is <=1000 and > 100" ' Test to see if x less than or equal to 2000 and greater than
   1000.
   
   Case 1001 To 2000 MsgBox "X is <= 2000 and > 1000" ' If none of the above tests returned true.
   
   Case Else MsgBox "X does not fall within the range" End Select End Sub , You can use Excel to accomplish this if you like.,, Paste the REPLACE(d) ALL code into the new module that pops up and separate the two macros with a single return line.,
2. 2

   Step 2: Note that the If...Then...Else statement executes a group of statements based on the value of an expression

3. 3

   Step 3: and the Select Case statement executes one of several of statements based on the value of an expression.

4. 4

   Step 4: Copy the above code into Word

5. 5

   Step 5: or a similar application

6. 6

   Step 6: and REPLACE ALL the "#** " with nothing(s).

7. 7

   Step 7: Open Excel and check Developer under Preferences on the Ribbon Option

8. 8

   Step 8: to enable you to work with macros.

9. 9

   Step 9: Click on the Developer tab on the Ribbon and select the VB Editor icon.

10. 10

    Step 10: Use the F5 or F8 key to run the code or the RUN menuitem.

Detailed Guide

About the Author