PDA

View Full Version : Userform vlookup, copy row data, display each found in sequential order in same form



BB55
09-20-2016, 11:36 AM
Ok, I have been fighting this for weeks and I'm getting frustrated. I have varied my search words so many times I am coming back around to the ones I first saw 3 weeks ago. I have a SS with two sheets 'Main' and 'Clients'. From Main I can select "View Existing clients" In the Facility text box (txtFacility) I want to make a partial entry...for instance 'clay' and hit search name. It will go to the 'Clients" sheet and begin searching in Column A Row 2 and when it finds 'clay' it will stop, and populate the userform from which I just hit search name. If this is not the 'Clay' I need, I hit 'Find Next" or 'Find Previous' and the next one in the clients sheet will populate the form, then the next, and so on. I have tried index/match, find/find next, vlookup, in all kinds of variations and ways of making it work. But I am just stumped. I can't imagine this is a difficult task for most of the readers here but it is a frustratingly difficult task for this beginner. I have attached a stripped down version. My Original Clients list is over 700 rows. I even tried copying off the rows to a seperate Row to at least complete the search and then worry about the display in the userform later but I cant seem to even get that to work. Any help would be appreciated. If you look at the rest of the code, I admit it's not pretty but it works. :) 17123. I hope that attachment is done correctly. Thanks in advance

mikerickson
09-20-2016, 10:43 PM
This is a very simple example (the "populate the form" part of your user form will be more complicated)
But once you know foundCell, filling in the userform should be simple for you to set up.


' in userform code module

Private Sub butFind_Click()
Dim FoundCell As Range
Dim PreviousFound As Range

On Error Resume Next
Set PreviousFound = Range(butFind.Tag)
On Error GoTo 0

With Sheet1.Range("A:A")
If PreviousFound Is Nothing Then Set PreviousFound = .Cells(Rows.Count, 1)
Set FoundCell = .Find(what:=TextBox1.Text, after:=PreviousFound, lookat:=xlPart, MatchCase:=False)
End With

If FoundCell Is Nothing Then
TextBox2.Text = "not found"
butFind.Tag = vbNullString
Else
TextBox2.Text = FoundCell.Value
TextBox3.Text = FoundCell.Offset(0, 1).Value
butFind.Tag = FoundCell.Address(, , , True)
End If
End Sub

Private Sub TextBox1_Change()
TextBox2.Text = vbNullString
TextBox3.Text = vbNullString
End Sub

Private Sub butClose_Click()
Unload Me
End Sub