VBA finds the last occurrence of a string

Question

I’ve been trying to solve this problem for several days now and I just can’t get around it. I searched several websites (even here several times), I saw that many people asked this question, but solutions for me do not work.

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.

While I have it

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 that seems to me is either an "invalid classifier" or "an object variable or with a non-blocking variable"

Any help is appreciated

Answer 1

The first problem is that searchTerm is defined as a Range object. You must set the destination of the objects using the Set keyword. Thus, the assignment becomes Set searchTerm = Range("A1:A999")...

Secondly, you will get error messages if searchTerm not found, because searchTerm will be set to Nothing . We can avoid this problem by using a simple condition to check if a job found.

Therefore, your updated Sub might 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

What 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 that I used Install .