VBA find last occurrence of a string

Question

I've been trying to work this problem out for a few days now and I just can't get my head around it. I have searched multiple websites (Even here several times), I have seen that many people have asked this question but the solutions don't work for me.

I want to find the last occurrence of a string in a range and store the address in a variable and display a message telling me where it is.

So far I have this

Private Sub CmdBtnClockIt_Click()
  Dim job As String
  Dim searchTerm as Range

  job = CmbBoxJob.Value
  searchTerm = Range("A1:A999").find(what:=job, searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
  MsgBox "last cell is " & searchTerm.Address
End Sub

All I seem to get is either "invalid qualifier" or "object variable or with block variable not set"

Any help is appreciated

Answer 1

The first issue is that searchTerm is defined as a Range object. You must set object assignments using the Set keyword. So the assignment becomes Set searchTerm = Range("A1:A999")...

Secondly, you will hit error messages if the searchTerm is not found, because searchTerm will be assigned a value of Nothing. We can avoid this issue by using a simple condition to check if the job was found.

Therefore your updated Sub may look something like this:

Private Sub CmdBtnClockIt_Click()
  Dim job As String
  Dim searchTerm As Range

  job = CmbBoxJob.Value
  Set searchTerm = Range("A1:A999").Find(what:=job, searchorder:=xlByColumns, searchdirection:=xlPrevious)
  If searchTerm Is Nothing Then
    MsgBox "Text was not found"
  Else
    MsgBox "Last cell is " & searchTerm.Address
  End If
End Sub

Answer 2

How about:

Sub dural()
    Dim job As String
    Dim searchTerm As Range
    job = "Happiness"
    Set searchTerm = Range("A1:A999").Find(what:=job, after:=Range("A1"), searchorder:=xlByColumns, searchdirection:=xlPrevious)
    MsgBox "last cell is " & searchTerm.Address
End Sub

Notice I used Set.