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

[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?