Excel tips and tricks
Note: this Excel howto applies to Excel 2003. Other versions may be different.
Suppose you maintain a list of expenses, something like this:
|2009-06-07||pet food||100||We R Pets|
|2009-07-07||oil change||40||Slimy Boys|
It's August now, and you need a new haircut. You want to search backwards in your spreadsheet and find out when and where you had your last haircut. How do you do that? There are two ways:
- manually, you can press Ctrl+F, type "haircut" and hold Shift while clicking "Find Next". This undocumented behavior in Excel 2003 will find the Previous occurrence instead.
- with the little Visual Basic macro below, you can just press a shortcut key of your choice and Excel will automatically search backwards for the content of the cell you're on, in its column:
Sub Search_cell_backwards() ' ' Search for the contents of the current cell in the cells above it. Only considers the first cell of ' the selection, because the .Find only takes into account the first cell of multi-cell selections ' With Selection.Worksheet ' Select the range ending just above the current cell Set targetColumn = .Range(.Cells(1, Selection.Column), .Cells(Selection.Row - 1, Selection.Column)) End With Set c = targetColumn.Find(Selection.Value, LookIn:=xlValues:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious) If c Is Nothing Then MsgBox ("Not found: " & Selection.Value) Else c.Select End If End Sub
To install this macro, press Alt+F11 in Excel. This will display the Visual Basic code for any macros you may have defined. If so, find your macros and paste the one above in the same place. If not, then right click on your worksheet in the list at the top left, and choose Insert -> Module.
To assign a keyboard shortcut to a macro, go in your worksheet to Tools -> Macro -> Macros (Alt+F8), select the macro, then click "Options".
Now you can place the cursor on a cell and press a hotkey to search backwards for that cell's contents.
The Find method only cares about the first cell in a selection. In other words, if you select the two cells
haircut | 8, you'll find the
haircut cell that is followed by
40 (even if the range was extended to the width of the selection). This is not what we want. The macro below searches for all the cells in a selection, including for a single cell, so it completely supersedes the previous macro.
Sub Search_selection_backwards() ' Search backwards for all the cells in the current selection ' With Selection For rw = .Row - 1 To 1 Step -1 entireSelectionFound = 1 For c = 1 To .Columns.Count If .Worksheet.Cells(rw, .Column + c - 1).Value <> .Cells(1, c).Value Then entireSelectionFound = 0 Exit For End If Next c If entireSelectionFound = 1 Then .Worksheet.Range(.Worksheet.Cells(rw, .Column), .Worksheet.Cells(rw, .Column + .Columns.Count - 1)).Select Exit For End If Next rw If entireSelectionFound <> 1 Then MsgBox ("Couldn't find selection starting with: " & .Cells(1, 1).Value) End If End With End Sub
Simply press Alt+Enter.
To keep C columns and R rows from scrolling when you scroll the worksheet, click on row R+1 and column C+1 and choose Window -> Freeze panes
Solution: to select all cells in the sheet, click the top-left-most cell, above 1 and to the left of A.
Showing changes from previous revision.