More Short Cut Keys...

Ctrl+D
Copy the cell above.

To perform auto-fill without manually dragging down using the mouse:
1. Place the mouse cursor at the bottom-right hand corner of the cell that you want to auto-fill with.

2. Double click.

A happy day

a

Book List

I would like to share some books which I find quite useful.

This book gives a good explanation of the more commonly used Excel objects. I highly recommend this book to novice Excel VBA programmers.

Excel Hacks (TM)

The information shared here is extracted from EXCEL HACKS: 100 Industrial-Strength Tips & Tools, from O'Reilly, by David and Raina Hawley. They are tips which I think are really useful. Hope you will find it as helpful as I do.

Hack#83: Use CodeName to Reference Sheets in Excel Workbooks

Sometimes you need to create a macro that will work even if the sheet names it references change. If you recorded a macro in Excel that references a specific sheet in your workbook, you know the code will continue to work only if the sheet name(s) remain the same. For example, if your worksheet is named Budget, and the code in your macro reads Sheets("Budget").Select and then you change the worksheet name, the macro will no longer work. This is because the macro recorder generates code based on the sheet's tab name or on the name you see when working in Excel.

To overcome this limitation, you have two options, the first of which is to use index numbers. A sheet's index number is determined by its position in the workbook. The leftmost sheet will always have an index number of 1, the next worksheet immediately to the right will always have an index number of 2, and so on. Excel VBA enables you to specify any sheet by using its index number, but unfortunately Excel does not use this method when you record a macro.

Also, although using an index number such as Sheets(3).Select is a better option than using Sheets("Budget").Select, the sheet's position in the workbook could change if you add, remove, or move sheets.

Instead of using index numbers, savvy VBA coders use CodeNames. Each sheet in a workbook is given a unique CodeName that does not change even when that sheet is moved or renamed, or when any other sheets are added. You can see a sheet's CodeName only by going into the VBE (select Tools->Macro->Visual Basic Editor, or press Alt-F11) and then displaying the Project window if necessary (select View->Project Explorer or press Ctrl-R).

The CodeName for the sheet with a tab name of Budget is, say Sheet3. A sheet's CodeName is always the name that appears outsid the parenthesis when you look in the Project Explorer. You can reference this sheet with VBA code in the workbook by using Sheet3.Select, as opposed to Sheets("Budget").Select or Sheets(3).Select.

If your workbook is already full of VBA code, recorded or written, that does not use a CodeName, you can change the code at the project level (all code in all modules in the workbook) by selecting Edit->Replaced... while in the VBE.

Caution: The only time you cannot use a sheet's CodeName is when you reference a sheet that is in a workbook different from the one in which the code resides.

Row Height Cannot AutoFit

To set the row height to fit its content, usually one would double click on the line between that row and the row below it. However, at times it works and at times it doesn't.

Here is why:
Actually the row height does fit the content neatly. The unfitting row is actually an illusion. It is in fact related to the amount of zoom applied on the worksheet. Set the zoom to 100% and the height should fit just nicely to the content.

Short Cut Keys

For those who love the keyboard and hate the tiresome mouse clicks, here are some keystrokes worth remembering. They'll be a great time saver to your work.

A. To Move to...
1. First cell (top left) of working region: Ctrl+Home
2. Last cell (bottom right) of working region: Ctrl+End
3. First cell in a row: Home
4. First occupied cell in a row: Ctrl+LeftArrow
5. Last occupied cell in a row: Ctrl+RightArrow
6. Next worksheet: Ctrl+PageDown
7. Previous worksheet: Ctrl+PageUp

B. To Select...
1. Current region: Ctrl+* (Ctrl+Shift+8)
2. Entire worksheet: Ctrl+A
3. A contiguous block of cells (cells to select are just next to each other)
- Select the first cell of the block
- Hold down the Shift button and select the last cell of the block
4. Non-contiguous block of cells (cells to select may not be next to each other)
- Select any cells of the block
- Hold down the Ctrl button and select the next cell
- Repeat step ii until all cells are selected
5. A contiguous block of worksheets (worksheets to select are just next to each other)
- Select the first worksheet of the block
- Hold down the Shift button and select the last worksheet of the block
6. Non-contiguous block of worksheets (worksheets to select may not be next to each other)
- Select any worksheet of the block
- Hold down the Ctrl button and select the next worksheet
- Repeat step ii until all worksheets are selected

C. Formatting...
1. Cell: Ctrl+1

D. Editing...
1. Undo last action: Ctrl+Z
2. Redo last action: Ctrl+Y
3. Fill a selected group of cells with the same value/formula: Ctrl+Enter
4. Change the same range of cells on different worksheets to the same value/formula:
- Select all the worksheets to edit (see Section B3 to B6)
- Enter the value/formula
- Press Ctrl+Enter

Stay tune for more short cut keys...

Some Nerdy Tips

>

Evaluation for Conditional Formatting

Evaluating a condition in a cell and applying a format accordingly maybe seem to be rather straight forward. But somethimes thing may prove to be a little tricky.

Suppose column A contains a list of Expiry Dates which you want to keep track, and conditional formatting is applied to the whole column which includes blank cells.

Conditions:
1. If the date today is earlier than the expiry date then the cell is White.
2. If the date today is equal to the expiry date then the cell is Yellow.
3. If the date today is later the expiry date then the cell is Red.

Since these three conditions are mutally exclusive, what you would enter as the evaluation formula will probably look like this:
Condition 1: =A1=TODAY( )
Condition 2: =A1<TODAY( )

Result:
The result that you will get may be a little unexpected. When the date today is earlier, equal or later than the expiry date, the cell will be White, Yellow and Red respectively. However, the rest of the remaining blank cells in the column will turn Red too. This is because blank cells will be evaluated to TRUE with A1<TODAY( ).

There is actually a fourth condition, that is, a cell can be a blank cell.

Therefore:
An extra condition to evaluate whether a cell is empty has to be added.

Condition 1: =A1="", No change to format
Condition 2: =A1=TODAY( )
Condition 3: =A1<TODAY( )

Take note that A1="" must be placed before <TODAY( ) so that empty cells will remain White.