VBA excel - return the last matching value in a column using VBA

Question

Basically, I have a rather large (and growing) sheet of position details and I'm looking to build in a sub routine that, once a position number is entered into the relevant cell, will auto-populate the corresponding cells in the row. VLOOKUP would do the trick nicely except, when a position has multiple lines, it returns the earliest set of details--I need it to return the latest.

I can produce the answer I need using a LOOKUP function , but I can't seem to translate the function across to VBA.

Example lookup function:

LOOKUP(D17,1/($D$2:$D$10=D17),E2:E10)

This is what I have so far

Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Target.Column = 4 Then
        actionrow = Target.Row

        resulte = Application.WorksheetFunction.Lookup(2, 1 / Range("D2:D10") = Target.Value, Range("E2:E10"))

        If Target.Value <> "" Then
            Range("E" & actionrow).formula = resulte
        End If
    End If

End Sub

user4039065 · Accepted Answer · 2016-02-23 06:25:02Z

1

I think that looking at column D for a matching value with the Range.Find method would do. Start at the Target cell and use the SearchDirection:=xlPrevious option. Something will always be found. If the row it is found is not the same row as Target then use the value in column E to populate the cell right of Target.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Columns(4), Target) Is Nothing Then
        On Error GoTo bm_Safe_Exit
        Application.EnableEvents = True
        Dim trgt As Range, lastrw As Long
        For Each trgt In Intersect(Columns(4), Target)
            lastrw = Columns(4).Find(what:=trgt.Value, after:=trgt, _
                                    lookat:=xlWhole, SearchDirection:=xlPrevious).Row
            Debug.Print lastrw
            If lastrw <> trgt.Row Then
                trgt.Offset(0, 1) = Cells(lastrw, trgt.Column + 1).Value
            End If
        Next trgt
    End If
bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

This should survive pasting multiple values into column D.

answered Feb 23, 2016 at 6:25

user4039065

Sign up to request clarification or add additional context in comments.

3 Comments

Gareth Davis Over a year ago

Thanks so much @Jeeped. So to repeat the process across multiple columns I just changed the trget.offset coordintes and trgt.column offset (to 2 and 3 and 4 and so on) and it works perfectly! This is going to be so useful. :)

Gareth Davis Over a year ago

Follow up question: How can I repeat the process for additional subsets of information on the same spreadshseet? I tried to modify the solution from @Jeeped but I'm having difficulty. The reason (from what I can tell) is because the solution operates by looking at all changes made on the spreadsheet and if they aren't in the specific column I want (column 4 in this case), the code goes to a 'safe_exit'. I think I need to modify the code so that instead of a 'safe_exit' it will look at another column (kind of like a series of IF functions). I have no idea how to do that though.

Gareth Davis Over a year ago

I am looking for solutions and will play a bit more, but I'm really struggling. :( Any help or suggestions of articles etc would be appreciated.

Yaroslav Zharkov · Accepted Answer · 2020-11-23 10:40:35Z

0

You can use .Find function with parameter SearchDirection:=xlPrevious

For case where you are searching word "AC" in a row 4:

 Set FindCell = sh_wb_SF.Range("4:4").Find(What:="AC", LookIn:=xlValues, SearchDirection:=xlPrevious)
        If FindCell Is Nothing Then
            MsgBox ("Ooooooopppps")
        End If

answered Nov 23, 2020 at 10:40

Yaroslav Zharkov

431 silver badge7 bronze badges

Collectives™ on Stack Overflow

VBA excel - return the last matching value in a column using VBA

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related