Join Curt Frye for an in-depth discussion in this video Selecting which action to take using a Case statement, part of Learning VBA in Excel (2010).
The If-Then code construct lets you determine which code segments to run based…on rules you define,…but its syntax is a bit convoluted.…If you need to run a brief segment, such as assigning a value to a variable based…on another variable's value,…you can use a Case statement.…A Case statement is more compact than the If-Then statements, and frankly you are…less likely to make mistakes while you're typing it.…I'll show you what the code looks like. Pressing Alt+F11.…As before, we're selecting a commission rate based on the amount of a sale.…So we have the variable of Total as Currency. Again, we're looking at the value…in cell C5, and we're assigning that value, the ActiveCell's value, to the Total variable.…
To begin the Case statement, you type Select Case and then the name of the variable.…In this case, it's Total.…At the end, you type End Select.…Everything else in between is a series of comparison operations, so that you can…decide into which case your data falls.…So first, in the case where the value is greater than or equal to 10,000, the…
- Working in the Visual Basic Editor
- Adding code to a macro
- Creating, exporting, and deleting code modules
- Declaring and using variables
- Managing variable scope
- Defining arrays
- Managing workbooks and worksheets with VBA
- Repeating tasks with loops
- Debugging VBA code
- Cutting, copying, and pasting cell data
- Running and triggering event procedures
Skill Level Advanced
Excel 2016: Power Query (Get & Transform)with Oz du Soleil1h 49m Intermediate
1. Introducing Visual Basic for Applications (VBA)
2. Defining Variables, Constants, and Calculations
3. Adding Logic to Your VBA Code
4. Debugging Your VBA Code
5. Managing Workbook Elements and Data in VBA
6. Adding Advanced Elements to Your Workbook
7. Using Excel Events in Your VBA Code
8. Putting It All Together
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.