Hi there
I have been searching, trialling (and failing) for a solution to my problem for three weeks now, but I can’t seem to solve it as I’m a complete VBA beginner! But I’m loving learning new things as I go.
I am trying to find a way to search for the most recent matching row entry (in the same sheet) when a new text value is entered via a UserForm on column A. Then, copy cells B:E into the active row.
To explain my goal step-by-step:
- When a blank cell in column A is clicked, the UserForm named NewEntry is called.
- When a user selects an existing name from the ListBox and clicks submit on the UserForm, it populates the active cell with this selection. Alternatively, the user can freetext a new name entry into the TextBox and click submit to populate the active cell.
- What I want to do next (and I can’t figure this out) is:
- When a new entry is submitted, search up the spreadsheet to find the most recent (i.e. the highest row number) name match in column A.
- If a match is found, copy this matching row data from column B to column E (i.e. not the entire row)
- Then, paste this matching data into the same range in the active row. The intent is to pre-fill information for users to avoid excess data entry.
I’ve been fumbling around with the following code placed in the UserForm itself:
Sub FindAndCopyAdjacent()
Dim Cell As Range
Set Cell = Range(ActiveCell).Find(What:=TextName.Text & ListName.Text)
If Not Cell Is Nothing Then
Range(Cell, Cell.Offset(0, 1)).Copy Destination:=("B:H")
'need more info to determine destination range
End If
End Sub
But I’m sure this is way off track.
I would be so incredibly grateful if someone could help me.
I have attached a sample workbook.
Bookmarks