ExtraCheese
New Member
- Joined
- Sep 18, 2020
- Messages
- 16
- Office Version
- 2016
- Platform
- Windows
So the situation is as following:
User adds entries with Userform, containing: ID, Name, Address, reference.
All the input data is stored in a different hidden tab.
Now I would want to create a function in a different userform where user can also search the data, for example he searches for the ID, and gets the correspondent name, address and reference. Each data entry is unique, so he should only get one result.
I have this code, but the search function does come up with the wrong results (ID nr; other address etc.).
User adds entries with Userform, containing: ID, Name, Address, reference.
All the input data is stored in a different hidden tab.
Now I would want to create a function in a different userform where user can also search the data, for example he searches for the ID, and gets the correspondent name, address and reference. Each data entry is unique, so he should only get one result.
I have this code, but the search function does come up with the wrong results (ID nr; other address etc.).
Private Sub CommandButton1_Click()
Dim id As Long, rowcount As Integer, foundcell As Range
id = TextBox3.Value
rowcount = Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
With Worksheets("Data").Range("A2:H" & rowcount)
Set foundcell = .Find(what:=id, LookIn:=xlValues)
If Not foundcell Is Nothing Then
TextBox1.Value = .Cells(foundcell.Column, 1)
TextBox2.Value = .Cells(foundcell.Column, 2)
TextBox4.Value = .Cells(foundcell.Column, 3)
TextBox5.Value = .Cells(foundcell.Column, 7)
TextBox6.Value = .Cells(foundcell.Column, 8)
TextBox7.Value = .Cells(foundcell.Column, 5)
TextBox8.Value = .Cells(foundcell.Column, 6)
Else
TextBox1.Value = ""
TextBox2.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""
TextBox7.Value = ""
TextBox8.Value = ""
End If
End With
End Sub