Help with Find and replace code

[Othafa]

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

[georgiboy]

One method might be:


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

[Paul_Hossler]

Probably more elegant and more efficient ways to do this




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

[Othafa]

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

[Othafa]

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.