Browse » Home
Sunday, July 22, 2012
How to Format an 'If Statement' in Excel for a Blank or Filled Reference
1. Open the Microsoft Excel 2010 worksheet. Press 'Alt F11' to open the VBA console.
2. Select a module from the list on the left side. If no module exists, right-click on a worksheet in the list, move the mouse pointer over 'Insert' and choose 'Module.' Select the module that appears.
3. Type 'sub testing()' into the first line of the module and press 'Enter.' Change 'testing' to whatever word you want as it is simply the name of the subroutine. Excel VBA automatically adds the 'End Sub' command to the last row of the module.
4. Type 'Dim x as range' into the next line. This will establish 'x' as a variable, which you will need in the next statement.
5. Type 'For each x in range ('XX:YY')' into the next line. Change 'XX' to the top-left cell in the range you want to work with and 'YY' to the bottom-right cell. This will create a loop that will go through each cell in the range one by one.
6. Type 'If IsEmpty(x) Then XXX Else YYY' into the next line. Change 'XXX' and 'YYY' to whatever code you want to run. The IsEmpty command will check each cell and perform the action after 'Then' if the cell is empty. 'Else,' and the code after it, are optional.
7. Type 'Next' on the next line. This tells Excel to loop back to the 'For' statement and move on to the next cell.