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

 

[DanteAmor]

What is "DISTRIBUTION_SET_G-L_CODING" is it a table or a named range?
Is the name correct?

  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)

You can remove the On Error Resume Next statement from the code, try and see what error comes up and on which line of the macro it stops.

Another way would be to search the sheet and the column. What is the name of the sheet and in which column to do the search.

 

[DanteAmor]

I assume it is a table, check that the name is correct: "DISTRIBUTION_SET_G_L_CODING".
Try the following:

Private Sub btnSearch_Click()
  Dim f As Range
  
  Set f = Range("DISTRIBUTION_SET_G_L_CODING[Record]").Find(txtvendor.Value, , xlValues, xlWhole)
  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
    txt.Value = f.Offset(0, 9).Value
  Else
    MsgBox "The number does not exist"
  End If
End Sub

 

[josros60]

Thank you.

is table "Table5"

 

[josros60]

Hi,

thank you for the code.

I tried it but getting this error:

Run-time error 1004
Method 'Range" of object'_Global' failed

and highlight this line:

Set f = Range("DISTRIBUTION_SET_G-L_CODING[Record]").Find(txtvendor.Value, , xlValues, xlWhole)

Thanks again.

 

[DanteAmor]

is table "Table5"

And should you look in the "Record" column?

Try:

Private Sub btnSearch_Click()
  Dim f As Range
  
  Set f = Range("Table5[Record]").Find(txtvendor.Value, , xlValues, xlWhole)
  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
    txt.Value = f.Offset(0, 9).Value
  Else
    MsgBox "The number does not exist"
  End If
End Sub

If it doesn't work, put here an image of your table where you can see the name of the table and its columns.

 

[josros60]

Hi,

here sample:

SAMPLE.xlsx
	A	B	C	D	E	F	G	H	I	J
1	SAGEID	VENDOR	CURRENCY	PO_NUMBER	APPROVER	GL_CODE	LINE_DESCRIPTION	TAX_GROUP	ACCOUNT#_TERMS	ORG_UNIT
2	CAN	CANADIAN NUMBERING ADMINISTRATION	Canadian Dollars		Wael Attalla/Blaisia Semega	611100-13-500-000 Local&Voip - Regulatory Charges : R - NWPLN - G	Annual share of costs - Numbering Administration in Canada f	HST 13%		Primus Approved by email
3	MACFUE	MACNAMARA FUELS	Canadian Dollars	INV 9430	Glen Gregory/Walter Bakovich	783110-03-535-000 Repairs & Maintenance: G - FIELD - G	OPERATIONAL VARIANCE - 90 DAYS INSPECTION LETTER	HSTON		Primus - Service Operations
4	MAGENT	MAGENTO AN ADOBE COMPANY	U.S. Dollars	NOV 10-2021-NOV 09-2022	BRAD FISHER/SHAUN RANDALL/NAV KAINTHRAI	760050-03-330-000 Software Licenses: G - MARK - G	GOLD SUPPORT NOV 09-2021-NOV 09-2022	Exemption Tax on US$	NET30	Primus - Marketing Web Costs
5	MALWAR	MALWAREBYTES INC.	U.S. Dollars	NOV 30 2021-NOV 29 -2022	Sheldon Pepin/George Zarif	760050-03-330-000 Software Licenses: G - IT - G	MALWAREBYTES INDENT SITE-NOV 30 2021-NOV 29-20222	HST 13% USD	NET30	Primus - SD
6	MASAHM	Masroor Ahmad Lone	Canadian Dollars		Gerry Vanderpost/Hayden Sookram	711143-03-215-000 Subcontractors - Outsource : G - FIN - G	CONTRACTOR FOR BILLING-MARCH 2022	Exemption Tax	DUR	Primus - Finance billing
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:J6	Expression	=COLUMN()=CELL("col")	text	NO
A2:J6	Expression	=CELL("row")=ROW()	text	NO

Thank you,

 

[DanteAmor]

I guess then that the table name is Table5 and you want to search in column "B" (Vendor).
If so, then try like this:

Private Sub btnSearch_Click()
  Dim f As Range
  
  Set f = Range("Table5[Vendor]").Find(txtvendor.Value, , xlValues, xlWhole)
  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
    txt.Value = f.Offset(0, 9).Value
  Else
    MsgBox "The number does not exist"
  End If
End Sub

If not, then you need to be more specific and provide the full information, or share your file for review.

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[josros60]

Hi,

Tried but when I search it complete the fields in the wrong text where it should vendor name put the PO number and so on (attaching image)

thank you

 

[DanteAmor]

I don't know what you are putting in the txtvendor textbox.
If I put "MACNAMARA FUELS" in the txtvendor textbox, according to your example in post #7, it puts the data correctly for me.

But from the beginning I am only guessing how your data is.
So help me to be able to help you, share your file to verify how you have the data.

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.