While rowCounter 75 Then _ Cells(rowCounter, 3) = "Pass" Else _ Cells(rowCounter, 3) = "Fail" rowCounter = rowCounter + 1 Wend
Once this VBA script is run, you see the results in the actual spreadsheet.Ī While Loop also loops through a series of statements, just like the FOR loop, but the condition of the looping to continue is a condition remaining true.įor example, you could write the same FOR loop above, as a WHILE loop, by simply using the rowCounter variable as follows.
So your VBA code would first have this line.įinally, you can create your FOR loop to step through every cell in that range and do the comparison.įor Each cell In rng If cell.Value > 75 Then _ Cells(rowCounter, 3) = "Pass" Else _ Cells(rowCounter, 3) = "Fail" rowCounter = rowCounter + 1 Next cell You’ll also need to define a counter so you can output the results to the appropriate row. To do this, you need to define the range and cell variables, and loop through them. To do this, you’d need to use the length of a range, and loop through that length by the number of rows that contain data. But what if you want to loop through an entire range of cells and do the same IF statement on each? IF statements are great for single comparisons, like the example above of looking at a single cell. Using this technique can often make code much easier to read and debug. If you don’t want the entire statement on a single line, you can split it into multiple lines by adding a “_” symbol at the end of the lines. One example would be to check if a grade in a spreadsheet is above or below a “passing” grade, and assigning the pass or fail status to another cell. These conditions can be joined by an AND or OR operator to link them together. The first part of the IF statement looks at whether a condition or set of conditions is true. VBA IF StatementsĪn IF statement works in VBA just like it works in any other programming language. When you add VBA code in the editor, always make sure to place your VBA code on the object and using the correct action that you want to use to trigger that code. Other worksheet actions you can use to trigger your VBA code include when the worksheet changes, when it’s closed (deactivated), when the worksheet calculation is run, and more. When you’re in the VBA editor, you need to use the two dropdown boxes at the top of the editing window to choose which object you want to attach the code to, and when you want the code to run.įor example, in Excel, if you choose Worksheet and Activate, the code will run whenever the worksheet is opened. The only difference are the objects you can reference and the actions you can take on those objects through the VBA code.īefore we dive into the different objects and actions you can take on them via VBA code, let’s first look at the most common VBA structure and syntax you can use when you write VBA code. The structure and syntax of the VBA code is the same across all applications. So, don’t be surprised by the different menus. In Word, you can add VBA code to documents. For example, in Excel, you can add VBA code to workbook or sheet objects. This is because the available objects where you can place VBA code depend on what objects there are in the application.