On Error Resume Next

There are alot of posts out there telling you to stay away from:

On Error Resume Next

But I say for simple macros, its the quick and incredibly easy way to stop macros behaving weirdly when someone does something (or uses it somewhere) unexpectedly. When you want to code a quick macro, you don't want to be worrying about sophisticated error trapping or coding for all scenarios. Because then its not quick. So just stick this simple line before any dodgy VBA calls and you or your users won't be faced with unexpected behaviours or subscripts out of range.

Sub FindValue()
Dim srchVal as String
Dim colHeading as String
Dim srchRange As Range
Dim foundRange As Range

srchVal = ActiveCell.Value
colHeading = Cells(1, ActiveCell.Column).Value

On Error Resume Next
ThisWorkbook.Sheets(colHeading).Activate
Set srchRange = ActiveSheet.Cells
Set foundRange = srchRange.Find(srchVal, , , xlWhole)
foundRange.Activate
End Sub

This macro searches for the value in the current cell in the sheet named at the top of its column. If there is no sheet name at the top of the column, it does weird things. But not with:

On Error Resume Next

It moves to the next line and sets the current sheet as the active sheet. So, simply and neatly, you just end up where you started. Of course its worth having a quick look at what that resumed next line of code could be coz sometimes it could end up taking you someplace weirder. And you could write lots of code to make sure this never happens, But really, its a macro - a quick and dirty way to get something repetitive done fast.