Find Last Value (not first)

[AL.W]

Hi. I have a command button on an excel sheet that takes the value in the active cell and searches for it on another sheet and then activates the found value.

Where there are multiple values, I'd like to activate the last cell with that value.

Here is my code, for which I'm getting a "compile error: invalid or unqualified reference" for .FindNext



Private Sub CommandButton1_Click()
Dim value As String 'Declare a string
value = ActiveCell.value 'Get the value of the selected Cell
Dim ws As Worksheet
'ws is the worksheet from we are searching the value
'You have to change myWorkSheetName for your worksheet name
Set ws = ThisWorkbook.Worksheets("WRITTEN")
ws.Activate
Dim c As Range 'Declare a cell
Set c = ws.Cells.Find(value, LookIn:=xlValues) 'Search the value
If Not c Is Nothing Then 'If value found
firstAddress = c.Address
Set c = .FindNext(c) 'here is where I get the error message
If c Is Nothing Then
GoTo DoneFinding
End If
Loop While c.Address <> firstAddress
End If
DoneFinding:
c.Activate 'Activate the cell, select it
Else
MsgBox "Not found" 'shows a message "Not Found"
End If
End Sub

[Bob Phillips]

You have a Loop, but not start (such as Do), and you have an Else outside of the If … EndIf construct.

To put it mildly, that code is a car-crash.

[Bob Phillips]

Not tested, but this should be better


Private Sub CommandButton1_Click()
Dim value As String
Dim ws As Worksheet
Dim c As Range

value = ActiveCell.value
Set ws = ThisWorkbook.Worksheets("WRITTEN")
ws.Activate
Set c = ws.Cells.Find(value, LookIn:=xlValues) 'Search the value
If Not c Is Nothing Then

firstAddress = c.Address
Do
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If

If c Is Nothing Then

MsgBox "Not found"
Else

c.Activate
End If
End Sub

[AL.W]

Hi xld,

Thanks for looking at this and helping me out of my car crash!

I've tried that code, and I still get an error message highlighting .FindNext
"Compile Error: Invalid or Unqualified Reference"

I've been playing with this a while, am new to vba coding so could be wrong, but I'm worried my version of excel/vba doesn't recognise .FindNext - is this even possible?

[AL.W]

for reference this is the code that is working and taking me to the first value in the sheet



Private Sub CommandButton1_Click()
Dim value As String
value = ActiveCell.value
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("WRITTEN")

ws.Activate
Dim c As Range
Set c = ws.Cells.Find(value, LookIn:=xlValues)

If Not c Is Nothing Then 'If value found

c.Activate
Else
MsgBox "Not found"
End If
End Sub

[Bob Phillips]

Your version definitely supports FindNext. The problem was that FindNext wasn't addressing a range, I should have spotted that. THe activate code was also going to activate the first found (because the loop ends when it gets back to that cell), so I have corrected that.

This should work for you.


Private Sub CommandButton1_Click()
Dim value As String, firstaddress As String, lastaddress As String
Dim ws As Worksheet
Dim c As Range

value = ActiveCell.value
Set ws = ThisWorkbook.Worksheets("WRITTEN")
ws.Activate
With ws.Cells

Set c = .Find(value, LookIn:=xlValues) 'Search the value
If Not c Is Nothing Then

firstaddress = c.Address
lastaddress = firstaddress
Do
Set c = .FindNext(c)
If c.Address <> firstaddress Then lastaddress = c.Address
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With

If lastaddress = vbNullString Then

MsgBox "Not found"
Else

Range(lastaddress).Activate
End If
End Sub

[AL.W]

Hi.

Thanks again for looking at this.

So when I run that code I get

gives run-time error '1004':
Application-defined or object-defined error

However, I have found a solution using search order (xlprevious) with the code I already have working, this starts the search from the bottom to the top.



Private Sub CommandButton1_Click()
Dim value As String
value = ActiveCell.value
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("WRITTEN")

ws.Activate
Dim c As Range
Set c = ws.Cells.Find(value, LookIn:=xlValues, SearchDirection:=xlPrevious)

If Not c Is Nothing Then 'If value found
c.Activate
Else
MsgBox "Not found"
End If
End Sub



I found my solution here, which looks pretty thorough on excel find

https://excelmacromastery.com/excel-vba-find/

thanks again :thumb

[Bob Phillips]

So when I run that code I get

gives run-time error '1004':
Application-defined or object-defined error

Worked fine for me when I tested it, but perhaps my test data doesn't mirror yours.

[AL.W]

Thanks again Bob.
Yes, I'm asking it to search a table with 50+ columns and 1500 rows so plenty of scope for problems.

I didn't want to ask you to do all my work for me, so was trying to grapple with your solution and work out why I was getting an error.

Was particularly intrigued by how this bit works


Set c = .Find(value, LookIn:=xlValues) 'Search the value
If Not c Is Nothing Then

firstaddress = c.Address
lastaddress = firstaddress
Do
Set c = .FindNext(c)
If c.Address <> firstaddress Then lastaddress = c.Address
Loop While Not c Is Nothing And c.Address <> firstaddress


In particular this bit blew my mind!


firstaddress = c.Address
lastaddress = firstaddress

can you describe what this is doing?

[Bob Phillips]

Okay, I'll give it a shot.

I assume that you understnad what firstaddress is foor, it was in your original code.

I did mention in one of my posts that your code was designed to find a matching value until it got back to the first matching value (it then knows it has exhausted all matches). The problem here is that you then activate the first match, not the last.

So I added another variable to get the address of the cell for every match. This variable gets initialised to the first match with this line

lastaddress = firstaddress
and gets updated every time there is a match, with this line

If c.Address <> firstaddress Then lastaddress = c.Address
Crucially, because of the test, it doesn't get updated when the FindNext goes back to the first match, so when you exit the loop, variable c is pointing at the first match, but lastaddress holds the address of the last match.

Does that make sense?