This would no doubt be one of Excel most under-utilised areas. It seems that when Excel users learn VBA they tend to forget the built-in features that are available to us in the Excel interface are still available in the Visual Basic environment. The examples I will show you can be applied to a lot of different situations and it will nearly always be worth sitting back and trying to think where they can be used. The easiest way to get the foundations for your code that makes use of one of Excels built-in features is without doubt by using the Macro Recorder, but it should only be a starting point not the code itself.
When using the Find in VBA it is a good idea to parse the result to a properly declared variable. The reason for this is so that we can check the result to see if the value we are looking for has been found. To do this we must also use the: On Error Resume Next Statement so that our Procedure does not produce a Run-time error. The examples below are just some of the ways I have used this feature in my VBA code of past. For all examples I will use the ActiveSheet, but the examples can (and often are) used on another sheet.
Dim lFound As Long .
On Error Resume Next
lFound = Cells.Find(What:="100", After:=Range("A1"), _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
If lFound = 0 Then
MsgBox "Cannot find 100"
MsgBox "100 is on row " & lFound
On Error GoTo 0
Dim iCount As Integer
Dim rFound As Range
On Error Resume Next
Set rFound = Range("A1")
Do the procedure directly above (BoldAllDogs) using a standard Loop and you will have time to make and drink a coffee! Yet I see people doing similar time and time again.
I will also suggest strongly that you read the help file on the Find Method as it describes each argument in good detail. If you do not read it you will at least need to know this:
The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you don’t specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.