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.
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.