AutoFill textbox on userform with exact value

Question

I want to auto fill two textboxes on my userform ,from a data of a sheet i have, but i want the code to find with the exact value and not just one part of it.

Ex: if i put the number "33" it returns a value, when the column have like "202409334", i want to assume a value only when the entire number is filled.

Private Sub txtdevolução_AfterUpdate()



Dim id As String, rowcount As Integer, foundcell As Range


id = txtdevolução.Value
    
    rowcount = Sheets("retornos pendentes").Cells(Rows.Count, 1).End(xlUp).Row
    
    With Worksheets("retornos pendentes").Range("A1:A" & rowcount)
        Set foundcell = .Find(what:=id, LookIn:=xlValues)
        
        If Not foundcell Is Nothing Then
            txtclienteopl.Value = .Cells(foundcell.Row, 3)
            txtmatricula2.Value = .Cells(foundcell.Row, 12)
         Else
            txtclienteopl.Value = ""
            txtmatricula2.Value = ""
            
        End If
        
    End With
    


End Sub

Ok, so what specific problem did you run into with your attempt? — braX, Mar 02 '23 at 19:38

score 1 · Accepted Answer · answered Mar 02 '23 at 23:57

Fill Userform Textbox

The current error was detected by CDP1802 in the comments namely you need to set the LookAt argument's parameter to xlWhole to not get partial matches.
Another issue is With Worksheets("retornos pendentes").Range("A1:A" & rowcount) when you later use .Cells(foundcell.Row, 3). With the search column being column A it works, but if you would change it, it would return the values from the wrong columns. Implementing .EntireRow with .Columns makes it more flexible and allows the use of the more user-friendly column strings.

Private Sub txtdevoluçao_AfterUpdate()

    Const WS_NAME As String = "retornos pendentes"
    Const FIRST_CELL As String = "A1"
    Const CLIENTEOP_COL As String = "C"
    Const MATRICULA_COL As String = "L"
    
    Dim rg As Range, fCell As Range, lCell As Range
    
    With ThisWorkbook.Sheets(WS_NAME)
        Set fCell = .Range(FIRST_CELL) ' First
        Set lCell = .Cells(.Rows.Count, fCell.Column).End(xlUp) ' Last
        Set rg = .Range(fCell, lCell)
    End With
    
    ' Reusing the variable...
    Set fCell = rg.Find(txtdevoluçao.Value, lCell, xlFormulas, xlWhole) ' Found
    ' ... short for:
    'Set fCell = rg.Find(What:=txtdevoluçao.Value, After:=lCell, _
        LookIn:=xlFormulas, LookAt:=xlWhole) ' Found
   
    If fCell Is Nothing Then
        txtclienteopl.Value = ""
        txtmatricula2.Value = ""
    Else
        With fCell.EntireRow
            txtclienteopl.Value = .Columns(CLIENTEOP_COL).Value
            txtmatricula2.Value = .Columns(MATRICULA_COL).Value
        End With
    End If

End Sub

Thank you for the suggestion, never thought about that. – André Lopes Mar 03 '23 at 03:15 — André Lopes, Mar 03 '23 at 03:15

AutoFill textbox on userform with exact value

1 Answers1

Fill Userform Textbox