Select a specific value from text box and search in a column of excel

[amarjeeth]

Dear All,

I have a worksheet "database" which is database of patient information in each row, in column H I have the "pateint IP number" I have a userform for search and copy. The textbox in the userform is "search_tb1", where I would input the required IP number and search in column H for a match, which should intern select the entire row of this selected cell and paste in another worksheet "preview" in row 2.

Can you please help me achieve this?

Thanks for your help.

Amar

 

[JLGWhiz]

This code is to be copied to the UserForm code module and will run when the TextBox is double clicked.

Private Sub search_tb1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range, fVal As Range
Set sh1 = Sheets("database")
Set sh2 = Sheets("preview")
If Me.search_tb1.Value <> "" Then
    lr = sh1.Cells(Rows.Count, "H").End(xlUp).Row
    Set rng = sh1.Range("H2:H" & lr)
        Set fVal = rng.Find(Me.search_tb1.Value, LookIn:=xlValues)
            If Not fVal Is Nothing Then
                fVal.EntireRow.Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
            End If
End If
End Sub