Userform Search

[josros60]

Hi,

have the code below attached to search button but when click nothing happen no error and no search.

here is the code:

Private Sub btnSearch_Click()
Dim RecordRow As Long
    Dim RecordRange As Range

    ' Turn off default error handling so Excel does not display
    ' an error if the record number is not found
    On Error Resume Next
        
        ' Find the row in the table that the record is in
        RecordRow = Application.Match(CLng(txtvendor.Value), Range("DISTRIBUTION_SET_G-L_CODING[Record]"), 0)
        
        ' Set RecordRange to the first cell in the found record
        Set RecordRange = Range("DISTRIBUTION_SET_G-L_CODING").Cells(1, 1).Offset(RecordRow - 1, 0)

        ' If an erro has occured i.e the record number was not found
        If Err.Number <> 0 Then
    
            ErrorLabel.Visible = True
            On Error GoTo 0
            Exit Sub
        
        End If
    
    ' Turn default error handling back on (Let Excel handle errors from now on)
    On Error GoTo 0
    
    ' If the code gets to here the record number was found
    ' Hide the error message 'Not Found'
    ErrorLabel.Visible = False
    ' and populate the form fields with the record's data
    txtvendor.Value = RecordRange(1, 1).Offset(0, 1).Value
    txtcurrency.Value = RecordRange(1, 1).Offset(0, 1).Value
    txtbPO.Value = RecordRange(1, 1).Offset(0, 1).Value
    txtapprover.Value = RecordRange(1, 1).Offset(0, 1).Value
    txtGLcode.Value = RecordRange(1, 1).Offset(0, 1).Value
    txtLineDes.Value = RecordRange(1, 1).Offset(0, 1).Value
    txttax.Value = RecordRange(1, 1).Offset(0, 1).Value
    txtAccTer.Value = RecordRange(1, 1).Offset(0, 1).Value
    txtOrgUnit.Value = RecordRange(1, 1).Offset(0, 1).Value
    txt.Value = RecordRange(1, 1).Offset(0, 1).Value
End Sub

thank you

 

[josros60]

here is a link to the file:

Box

app.box.com

thank you,

 

[josros60]

got it, but this need to type exactcly the name how about if just remember the start of the name and if I type wildcard like this:


macnam*

thanks

 

[DanteAmor]

have the code below attached to search button but when click nothing happen no error and no search.

That's your OP

The correct and tested code in your file is the one I put in post #8

got it, but this need to type exactcly the name how about if just remember the start of the name and if I type wildcard like this:

That's a new request, it's not in your OP.
I help you with a part, you can put the wildcard or just a part of the text.
The following code puts you the first match.

Private Sub btnSearch_Click()
  Dim f As Range
  
  Set f = Range("Table5[Vendor]").Find(txtvendor.Value, , xlValues, xlPart)
  If Not f Is Nothing Then
    txtcurrency.Value = f.Offset(0, 1).Value
    txtbPO.Value = f.Offset(0, 2).Value
    txtapprover.Value = f.Offset(0, 3).Value
    txtGLcode.Value = f.Offset(0, 4).Value
    txtLineDes.Value = f.Offset(0, 5).Value
    txttax.Value = f.Offset(0, 6).Value
    txtAccTer.Value = f.Offset(0, 7).Value
    txtOrgUnit.Value = f.Offset(0, 8).Value
  Else
    MsgBox "The number does not exist"
  End If
End Sub

If you require any other search, you will need to create a new thread.

 

[josros60]

I am really sorry won't do it again.

thank you so much for all your help, worked good now.

 

[DanteAmor]

You should actually mark the answer that solved your problem, unless your answer itself is the solution.

thank you so much for all your help, worked good now.

I'm glad to help you. Thanks for the feedback.

 

[smozgur]

thank you so much for all your help, worked good now.

The solution post has been changed accordingly.

@josros60, in your future questions, please mark the post as the solution that answered your question to help future readers. No further action is required in this question as I already changed the solution post.