Help with Find and replace code

Hi, any help appreciated with using VBA to find data in a table based on user input from a Drop Down Box.

I have attached a Sample Workbook

Data is contained in a Table on Sheet 2 (The real world table is bigger but I reckon if I can be shown how to make this work I'll be able to adapt it to the real world Table

I'd like the User to select a Name from a drop down list of names in Sheet 1 Cell "A6"

This should bring up the related data from Sheet2 in the next cells in that row for the user to then edit/change.

When the Update button is pressed the new data is copied back to the orriginal table.

I'm generally a "Record Macro" kind of user, which works in most cases I need it to but VBA seems reluctant to let me use a drop down box to find data elsewhere when I try to record a macro.

Thanks for any help.

Rob

One method might be:

Code:

---

Dim FoundRow As Long

Sub FindData()
    Dim FindName As String

    FindName = Sheet1.Range("A6").Value
    FoundRow = Sheet2.Range("B:B").Find(FindName, , xlValues, xlWhole).Row
    Union(Sheet2.Range("C" & FoundRow), Sheet2.Range("D" & FoundRow), Sheet2.Range("F" & FoundRow), Sheet2.Range("H" & FoundRow)).Copy
    Sheet1.Range("B6").PasteSpecial xlAll
    Application.CutCopyMode = false
End Sub

Sub ReturnRow()
    With Sheet2
        .Range("C" & FoundRow) = Sheet1.Range("B6")
        .Range("D" & FoundRow) = Sheet1.Range("C6")
        .Range("F" & FoundRow) = Sheet1.Range("D6")
        .Range("H" & FoundRow) = Sheet1.Range("E6")
    End With
End Sub

---

Place it in a module and point your buttons to the code. Keep the Dim at the top out of the two subs.

There would be quite a few ways of doing this but the above is a start.

Hope this helps

Probably more elegant and more efficient ways to do this

Code:

---

Option Explicit


Sub sName()
    Dim oList As ListObject
   
    Set oList = Worksheets("Sheet2").ListObjects("Table1")
   
    With Worksheets("Sheet1")
       
        On Error GoTo NiceExit
        .Cells(6, 2).Value = Application.VLookup(.Cells(6, 1).Value, oList.DataBodyRange, 2, False)
        .Cells(6, 3).Value = Application.VLookup(.Cells(6, 1).Value, oList.DataBodyRange, 3, False)
        .Cells(6, 4).Value = Application.VLookup(.Cells(6, 1).Value, oList.DataBodyRange, 5, False)
        .Cells(6, 5).Value = Application.VLookup(.Cells(6, 1).Value, oList.DataBodyRange, 7, False)


        Exit Sub
    End With
   
NiceExit:
End Sub




Sub uUpdate()
    Dim r As Long
    Dim oList As ListObject
   
    Set oList = Worksheets("Sheet2").ListObjects("Table1")
   
    With Worksheets("Sheet1")
        r = Application.Match(.Cells(6, 1).Value, oList.ListColumns(1).Range, 0)
       
        oList.ListRows(r - 1).Range.Cells(2).Value = .Cells(6, 2).Value
        oList.ListRows(r - 1).Range.Cells(3).Value = .Cells(6, 3).Value
        oList.ListRows(r - 1).Range.Cells(5).Value = .Cells(6, 4).Value
        oList.ListRows(r - 1).Range.Cells(7).Value = .Cells(6, 5).Value
    End With
   
    Exit Sub


NiceExit:
End Sub

---

Wow That was quick, and both solutions look elegant to my untrained eyes. Certainly much easier to follow than some of the code I'd tried to reconfigure for my needs.
Thank you both so much, made my life a whole lot easier

ok, follow up question.
I now need to have the option in the above code to select and then change the name selected to something else.
The way I would do this as a one off is find and replace but I can't find vba code for that which works with a variable "find what" option?
Thanks for any help.