Search any field

[josros60]

Hi,

I have a userform (pasting mini sheet) how to modify the file that I can search from any of the fields, also right now keep saying data no found and then display the correct information can it modify to display "data no found" only if is true that the record doesn't exist.


here is the code:

Private Sub CommandButton1_Click()
Dim x, y As Long

x = Sheets("DIST CODE").Cells(Rows.Count, 1).End(xlUp).Row
With UserForm1

    For y = 2 To x
 If Sheets("DIST CODE").Cells(y, 1).Value = Val(.TextBox1.Value) Then
 
    .TextBox1.Value = Sheets("DIST CODE").Cells(y, 1).Value
    .TextBox2.Value = Sheets("DIST CODE").Cells(y, 2).Value
    .TextBox3.Value = Sheets("DIST CODE").Cells(y, 3).Value
    .TextBox4.Value = Sheets("DIST CODE").Cells(y, 4).Value
    .TextBox5.Value = Sheets("DIST CODE").Cells(y, 5).Value
    .TextBox6.Value = Sheets("DIST CODE").Cells(y, 6).Value
    .TextBox7.Value = Sheets("DIST CODE").Cells(y, 7).Value
    .TextBox8.Value = Sheets("DIST CODE").Cells(y, 8).Value
    Exit Sub
    Else
       
      MsgBox " DATA NOT FOUND"
      

End If
Next y




End With
End Sub

NAVIGATA_DISTRIBUTION SETS-CODES.xlsm
	A	B	C	D	E	F	G	H
2	SAGEID	VENDOR	ACCOUNT #	LINE DESCRIPTION	G/L ACCOUNT	G/L DESCRIPTION	ORG UNIT	ANALYST
3	100215	ABC COMM.	17786-28380,28620, 30862	Fixed Data-Customer-Data Services-17786-	602110-28-500-100	Fixed Data - Customer - Data Services: C - TCOM - ONT	Navigata - Carrier Costing	MJT
4	100109	AFX COMMUNICATIONS	80017	LD - TOLL FREE OTHER SERVICES	603202-28-500-100	LD - TF - Other : C - TCOM - ON	Navigata - Carrier Costing	MJT
5	100176	AIRESPRING	1352791	LD - Toll DAL : C - TCOM - BC	603220-28-500-100	LD - Toll DAL : C - TCOM - ON	Navigata - Carrier Costing	MJT
6	100072	ANI NETWORKS	100226-1001	LD - Toll DAL : C - TCOM - BC	603220-28-500-100	LD - Toll DAL : C - TCOM - ON	Navigata - Carrier Costing	MJT
7	100226	ARIN	100226	LEGACY ANNUAL MAINTENANCE	601420-28-500-100	Internet - Other - General Internet Transit : C - TCOM - ON	Navigata - Carrier Costing	JM
8	100125	AT&T 831	831-000-5480-119,264,269	Local&VOIP - Other - Services	605130-28-500-100	Local&VOIP - Other : C - TCOM - ON	Navigata - Carrier Costing	MJT
9	100068	BELL TOLL FREE	CB001945	LD - TOLL FREE OTHER SERVICES	603202-28-500-100	LD - TF - Other : C - TCOM - ON	Navigata - Carrier Costing	MJT
10	100356	CCTS(COMMISSION FOR COMPLAINTS FOR TELECOM)			611100-28-500-100	Local&VOIP - Regulatory Charges C - TCOM - ON	Navigata - Carrier Costing	MJT
11	100120	CITY OF NELSON	8048	HALF RACK COLOCATION RENTAL PER MJTREEMENT	601432-28-500-100	Network Sites Utilities: C - TCOM - ON	Navigata - Carrier Costing	MJT
12	100313	CLOUD (CTI)	63015	EMBEDDED INTEGRATION/ADMIN FEES-	760050-28-550-100	Software Licences C - NSE ON	Navigata - Carrier Costing	MJT
13	100271	COMWAVE	L00004	Local&VOIP - Other - Services	605130-28-500-10	Local&VOIP - Other : C - TCOM - ON	Navigata - Carrier Costing	MJT
14	100116	COUNTRY COMM.	100116	LD - Toll DAL : C - TCOM - BC	603220-28-500-100	LD - Toll DAL : C - TCOM - ON	Navigata - Carrier Costing	MJT
DIST CODE

Cell Formulas
Range	Formula
G6,G12:G14,G8:G10	G6	=VLOOKUP([@SAGEID],Table2,5)
C9,C14	C9	=VLOOKUP([@SAGEID],Table2[[SAGE ID ]:[Org Unit ]],3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:H52,A53:E53,G53:H53	Expression	=COLUMN()=CELL("col")	text	NO
A3:H52,A53:E53,G53:H53	Expression	=CELL("row")=ROW()	text	NO

Thank you,

 

[Saurabhj]

Hi,

Please use code as below:

Private Sub CommandButton1_Click()
Dim x, y As Long
Dim valFound As String
valFound = "N"
x = Sheets("DIST CODE").Cells(Rows.Count, 1).End(xlUp).Row

With UserForm1
    For y = 2 To x
        If Sheets("DIST CODE").Cells(y, 1).Value = Val(.TextBox1.Value) Then
           valFound = "Y"
        
           .TextBox1.Value = Sheets("DIST CODE").Cells(y, 1).Value
           .TextBox2.Value = Sheets("DIST CODE").Cells(y, 2).Value
           .TextBox3.Value = Sheets("DIST CODE").Cells(y, 3).Value
           .TextBox4.Value = Sheets("DIST CODE").Cells(y, 4).Value
           .TextBox5.Value = Sheets("DIST CODE").Cells(y, 5).Value
           .TextBox6.Value = Sheets("DIST CODE").Cells(y, 6).Value
           .TextBox7.Value = Sheets("DIST CODE").Cells(y, 7).Value
           .TextBox8.Value = Sheets("DIST CODE").Cells(y, 8).Value
           Exit Sub
        End If
    Next y
End With

If valFound = "N" Then
      MsgBox " DATA NOT FOUND"
End If

End Sub

 

[dmt32]

Hi,

If it is your requirement to type a value in any one of the 8 textboxes & have the code search the appropriate column in your table (e.g. TextBox3 search Column ACCOUNT # ) for a match & return that rows data then see if this update to your code does what you want

Private Sub CommandButton1_Click()
    Dim wsDistCode  As Worksheet
    Dim m           As Variant, Search As Variant
    Dim i           As Long, c As Long
    
    Set wsDistCode = ThisWorkbook.Worksheets("DIST CODE")
    
    For c = 1 To 8
        Search = Me.Controls("TextBox" & c)
        If Len(Search) > 0 Then
            If IsNumeric(Search) Then Search = Val(Search)
            m = Application.Match(Search, wsDistCode.Columns(c), 0)
            For i = 1 To 8
                Me.Controls("TextBox" & i).Value = IIf(Not IsError(m), wsDistCode.Cells(CLng(m), i).Text, "")
            Next i
            Exit For
        End If
    Next
    
    If IsError(m) Then MsgBox Search & Chr(10) & " DATA Not FOUND", 48, "Not Found"
    
End Sub

Dave

 

[josros60]

Thank you for the suggestion, worked good.

if you don't mind one more question how to clear the data on the form to search for next one and also is possible to search using like FORTIS*.* and find anything starting with fortis.

thanks again.

 

[dmt32]

Hi,
If you are referring to my solution then you can, if making a text search, include the wild card in the TextBox

e.g.
FORTIS*

Solution will only return the first matched instance in the range.

Code will clear the form if no search match found but if want to add a clear button

Private Sub CommandButton2_Click()
    Dim i As Long
    For i = 1 To 8
        Me.Controls("TextBox" & i).Value = ""
    Next i
End Sub

Dave

 

[josros60]

Thank you so much, worked perfectly the wildcard search and the clear button.

 

[Fluff]

@josros60 in future please mark the post that solved your problem as the solution, not your post saying it worked. Thanks

 

[josros60]

Sorry, I will.

thank you