vba how to use find in Excel

Q162. In Excel, how do I use "Find" to determine last occurrence of a string in a WorkSheet range using VBA?

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:

  1. Option Explicit
  2. Sub FindLastOccurrence()
  3. Dim sPatient As String
  4. Dim rngSearch As Range
  5. Dim dtLastSessionDate As Date
  6. ''
  7. sPatient = "Devon"
  8. ''
  9. 'Set an Object variable for the range to be searched.
  10. Set rngSearch = ActiveSheet.Range("AQ5:AQ14").Find(What:=sPatient, SearchDirection:=xlPrevious)
  11. If Not rngSearch Is Nothing Then
  12. dtLastSessionDate = ActiveSheet.Cells(rngSearch.Row, rngSearch.Column + 1)
  13. End If
  14. ''
  15. 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) Vba code to password protect workbook in Excel

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

3) How can I identify the cells that influence a particular cell to help with debugging of my spreadSheet or understand a spreadSheet that I inherited?

4) How can I save a WorkSheet as a new WorkBook using VBA?

5) How can I loop through all WorkSheets and get their names using VBA?

6) How can I set up ListBox using VBA to allow users to select multiple values?

7) How can I get users to select a folder to save the output of my macro?

8) How do i put double quotes in a string in vba in Excel

9) How can I prevent users from seeing / accessing my macro code?

10) I have a macro that takes a lot of time for execution - how can I keep the user informed that the macro is running?

11) Find and count instances of a character in a string in Excel

12) How can I check if a file exists in a folder using VBA?

13) Userform initialize vs userform show in Excel

14) Vba length of an array in Excel

15) How can I filter and copy only filtered data using VBA?