Excel Made Easy

Vba how to use find in Excel

For example in the table below I want to find the date of the last session with Devon.

excel vba how to use find

To do it in Excel, here is the answer:

Option Explicit
Sub FindLastOccurrence()
Dim sPatient As String
Dim rngSearch As Range
Dim dtLastSessionDate As Date
''
sPatient = "Devon"
''
'Set an Object variable for the range to be searched.
Set rngSearch = ActiveSheet.Range("AQ5:AQ14").Find(What:=sPatient, SearchDirection:=xlPrevious)
If Not rngSearch Is Nothing Then
dtLastSessionDate = ActiveSheet.Cells(rngSearch.Row, rngSearch.Column + 1)
End If
''
End Sub

Description:

a) Line 10 is used to search the specified range. The "SearchDirection:=xlPrevious" argument find sets the search up in reverse direction. If xlPrevious is replaced by xlNext, the search would be from top to bottom.

You can find similar Excel Questions and Answer hereunder

1) How can I extract file name from a full path including folder path and file name?

2) Vba list all files in a folder in Excel

3) How can I protect / unprotect WorkSheet using VBA?

4) How can I get row count of filtered data?

5) How can I identify the cells that are dependent on a particular cell?

6) I have a complex WorkSheet with lot of fields and data - How can I quickly find the cells that have formulas?

7) How can I hide all comments in my WorkSheet using VBA?

8) How can I find the least common multiple using Excel?

9) How to copy files in Excel VBA is explained here

10) How do I add a symbol like Triangle / Inverted Triangle for indicating trends in a cell using VBA?

Vba how to use find in Excel

You can find similar Excel Questions and Answer hereunder

Here the previous and next chapter