Userform - Lookup and edit.

[rmak85]

Hello,
On my spreadsheet I have a column with "Test No."
I have a button that brings up a userform which allows me to input my test no which then populates the userform with the data from the spreadsheet for that particular test no.

At the moment it only allows me to lookup Numbers in my Test number textbox on the userform. i need to change:
"If IsNumeric(RTMotherRoll_Search_UserForm.TextBox1.Value) Then"
to allow me to include text.

Dim id As Integer, i As Integer, j As Integer, flag As Boolean
Sub RTMotherRollGetData() 'Change to same name within userform
If IsNumeric(RTMotherRoll_Search_UserForm.TextBox1.Value) Then 'Change to same userform name
     flag = True
     i = 5 'start at row
     id = RTMotherRoll_Search_UserForm.TextBox1.Value 'Change to same userform name
     
     Do While Cells(i + 1, 2).Value <> "" 'starting cell, means (row+1, col)
         If Cells(i + 1, 2).Value = id Then 'starting cell, means (row+1, col)
             flag = True
             For j = 2 To 72 'Change to how many boxes on userform
               RTMotherRoll_Search_UserForm.Controls("TextBox" & j).Value = Cells(i + 1, j).Value 'Change to same userform name
             Next j
         End If
         i = i + 1
     Loop
     If flag = False Then
         For j = 2 To 72 'Change to how many boxes on userform
             RTMotherRoll_Search_UserForm.Controls("TextBox" & j).Value = "" 'Change to same userform name
         Next j
     End If
Else
     RTMotherRollClearForm 'Change to same name Below
End If
End Sub

 

[rmak85]

Hello, sorry for double posting, I have rewrote the code:
My data starts at b6, (test no, type, id....) along the columns
The code lets me input the test no. into my userform textbox "searchtxt" and it inputs the data from the worksheet into the userform. In The userform i can then edit and click a button to update it on my worksheetsheet. However if i type a test no. not on my worksheet i get debug error message "run-time error 91"
I need to add another a bit of code to prevent the error popup or a msg saying no test found.

Private Sub searchtxt_Change()

    lastrow = Range("b65536").End(xlUp).Row
    
    findrow = Range("b1:b" & lastrow).Find(searchtxt.Value, Cells(6, 2)).Row
    For I = 1 To 81
        UserForm1.Controls.Item("TextBox" & CStr(I)).Value = Cells(findrow, I).Value
    Next I
     
End Sub

 

[rmak85]

Sorry again, I have figured a solution, My code looks if the value exists 1st, if not a msg pops up, if true then proceeds to populate the data on my userform.
Here is the code incase anyone will find it useful in the future.

Private Sub searchtxt_Change()

    'Check to see if value exists
    If WorksheetFunction.CountIf(Sheet2.Range("B:B"), Me.searchtxt.Value) = 0 Then
    MsgBox "This is an incorrect No."
    Me.searchtxt.Value = ""
    Exit Sub
    End If
    'Lookup data
    lastrow = Range("b65536").End(xlUp).Row
    findrow = Range("b1:b" & lastrow).Find(searchtxt.Value, Cells(6, 2)).Row
    For I = 1 To 81
        UserForm1.Controls.Item("TextBox" & CStr(I)).Value = Cells(findrow, I).Value
    Next I
        
End Sub