Userfrom testbox 1 in put 68528 textbox 2 i wanted to pull the data based on input in textbox1 from the data table on sheet RRS feed

  • Question

  • Textbox 1= 68528 

    textbox2 =__________

    i wanted the result in textbox 2 based on input in textbox1 from data set on workbook.

    this through vba userform .

    • Edited by sunny10000 Sunday, January 12, 2020 4:51 PM
    Sunday, January 12, 2020 4:50 PM

All replies

  • Assuming that your data is something like the screen shot then the code below will run immediately the TextBox1 is populated with a value and find the entered value and insert the value in the adjacent column into TextBox2. If data not found then a MsgBox to the user accordingly.

    Note that the columns do not need to be adjacent. You can have other data between the found data and the data to return. Just edit the Offset to the required column. See comments in the code re getting the correct offset.

    Code to perform the required action.

    Private Sub TextBox1_AfterUpdate()
        Dim wsData As Worksheet
        Dim rngToSearch As Range
        Dim rngToFind As Range
        Set wsData = Worksheets("Sheet1")   'Edit "Sheet1" to your data sheet name
        With wsData
            'Following line assigns from cell A2 to the last used cell in the column to a range variable.
            Set rngToSearch = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
        End With
        'Note when a range is assigned to a range variable
        'then the variable contains the workbook, worksheet and range of cells references
        'so there is no need to specify the worksheet reference again.
        With rngToSearch
            Set rngToFind = .Find(What:=Me.TextBox1.Value, _
                                    LookIn:=xlValues, _
                                    LookAt:=xlWhole, _
                                    SearchOrder:=xlByRows, _
                                    SearchDirection:=xlNext, _
                                    MatchCase:=False, _
            If Not rngToFind Is Nothing Then
                'The following line gets the cell to the right of the found value
                'And assigns it to TextBox2
                'Edit the Offset column to the required column.
                'The Offset is the number of times you would need to press the right arrow
                'from the found data in column A to the column with the required data.
                'The example code is offset 1 column.
                Me.TextBox2.Value = rngToFind.Offset(0, 1).Value
                MsgBox "Value in TextBox1 not found."
            End If
        End With
    End Sub

    Feel free to get back to me if not doing what you required.

    Regards, OssieMac

    • Edited by OssieMac Monday, January 13, 2020 5:12 AM Spelling corrected
    Monday, January 13, 2020 5:11 AM
  • Thank you for the response. I will try this code.
    Monday, January 13, 2020 4:13 PM