View Full Version : [SOLVED:] Help with Find and replace code
Othafa
01-25-2022, 07:00 AM
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
01-25-2022, 07:44 AM
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
01-25-2022, 11:04 AM
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
01-25-2022, 02:12 PM
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
02-09-2022, 03:08 AM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.