Excels Built-in Features In VBA Code. Excel Training VBA lesson 19

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.

                            SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                            MatchCase:=False)

            If rFound Is Nothing Then
              MsgBox "Cannot find 100"
          Else
            MsgBox rFound.Address
        End If
On Error GoTo 0
End Sub

Sub FindRow()
Dim lFound As Long .

On Error Resume Next
lFound = Cells.Find(What:="100", After:=Range("A1"), _

LookIn:=xlValues,LookAt:=xlWhole, _

                             SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                            MatchCase:=False).Row

        If lFound = 0 Then
             MsgBox "Cannot find 100"
       Else
            MsgBox "100 is on row " & lFound
        End If
On Error GoTo 0
End Sub

Sub LookUpLeft()
Dim sFound As String

On Error Resume Next
sFound = Columns(3).Find(What:="dog", After:=Range("C1"), _

LookIn:=xlValues,LookAt:=xlWhole, _

                            SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                            MatchCase:=False).Offset(0, -1)

            If sFound <> "" Then
                MsgBox "One column to the left of dog, on the same row is " & sFound
            Else
                MsgBox "No value found"
            End If
On Error GoTo 0
End Sub

                           SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                           MatchCase:=False)
                          rFound.Font.Bold = True
    Next iCount
On Error GoTo 0
End Sub

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.

EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Excel Training VBA Lesson 19

Excels Built-in Features In VBA Code

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL VBA LEVEL1 TRAINING INDEX

Excels Built-in Features

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL VBA LEVEL1 TRAINING INDEX