No result found

[xainthowell]

Everytime I click the search button it says no result found for "f/b" did i missed something in the code? Thanks

Private Sub cmdSearch_Click()
   
    Dim VesselName As String
    
        VesselName = (txtSearch.Text)
        lastRow = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
        
        For i = 2 To lastRow
        
            If Worksheets("Data").Cells(i, 1).Value = VesselName Then
            txtVName.Text = Worksheets("Data").Cells(i, 2).Value
            txtGT.Text = Worksheets("Data").Cells(i, 3).Value
            txtNT.Text = Worksheets("Data").Cells(i, 4).Value
            End If
        Next
        
         If Not found Then
            MsgBox "No results found for '" & txtSearch.Value & "'.", vbInformation, "Search Results"
         End If
    
End Sub

Fishing Vessel Data Record.xlsm
	A	B	C	D	E
1	ID	NAME OF FISHING VESSEL	GT	NT	CO
2	1	F/B MM (MOTHER BOAT)	33.2	22.57	OK
3	2	F/B MM 2 ( FISH CARRIER)	7.98	5.42	OK
4	3	F/B MM 3 ( FISH CARRIER)	4.44	3.02	OK
5	4	F/B JOSHUA MARI 88 (MOTHER BOAT)	40.07	27.24	OK
6	5	F/B JOSHUA MARI 9 (FISH CARRIER)	8.42	5.72	OK
7	6	F/B JOSHUA MARI 7 (MOTHER BOAT)	38.95	26.5	OK
8	7	F/B JOSHUA MARI 14(FISH CARRIER)	11.99	8.15	OK
9	8	F/B JOSHUA MARI 6 (FISH CARRIER)	6.02	4.08	OK
10	9	F/B JOSHUA MARI 2 (FISH CARRIER)	9.61	6.54	OK
11	10	F/B JOSHUA MARI 3 (FISH CARRIER)	10.82	7.35	OK
12	11	F/B JOSHUA MARI IV(FISH CARRIER)	8.01	5.44	OK
13	12	F/B JOSHUA MARI 12 (FISH CARRIER)	12.09	8.22	OK
14	13	F/B JOSHUA MARI 16 (MOTHER BOAT)	46.3	31.48	OK
Data

Cell Formulas
Range	Formula
A2:A14	A2	=ROW()-1

 

[Phuoc]

Private Sub cmdSearch_Click()
  
    Dim VesselName As String
   
        VesselName = Val(txtSearch.Text)
        lastrow = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
       
        For i = 2 To lastrow
       
            If Worksheets("Data").Cells(i, 1).Value = VesselName Then

            txtVName.Text = Worksheets("Data").Cells(i, 2).Value
            txtGT.Text = Worksheets("Data").Cells(i, 3).Value
            txtNT.Text = Worksheets("Data").Cells(i, 4).Value
            Exit For
            End If
        Next
       
         If i > lastrow Then
            MsgBox "No results found for '" & txtSearch.Value & "'.", vbInformation, "Search Results"
         End If
   
End Sub

 

[James006]

Hi,
Difficult to guess your context ...
But ... may be a potential help : Left(UPPER(yourcell),3) ="F/B"

 

[xainthowell]

Hi,
Difficult to guess your context ...
But ... may be a potential help : Left(UPPER(yourcell),3) ="F/B"

what I mean is when is search "F/B MM" is says no result found for "F/B MM"

 

[James006]

what I mean is when is search "F/B MM" is says no result found for "F/B MM"

So, to specifically tackle this point ... you need to search for yourtext & "*" ... in order to ensure a thorough scan

 

[xainthowell]

So, to specifically tackle this point ... you need to search for yourtext & "*" ... in order to ensure a thorough scan

Nothings change still the same result

[RANGE=rs:13|cs:4|v:table|w:Fishing Vessel Data Record.xlsm|cls:xl2bb-200|s:Data|tw:430][XR][XD=ch:0|w:63|cls:h][/XD][XD=ch:0|w:219|cls:h][/XD][XD=ch:0|w:74|cls:h][/XD][XD=ch:0|w:74|cls:h][/XD][/XR][XR][XD=h:c|v:m|bc:808080|ch:31.5]ID[/XD][XD=h:c|v:m|fw:b|bc:D6DCE4]NAME OF FISHING VESSEL[/XD][XD=h:c|v:m|fw:b|bc:DDEBF7]GT[/XD][XD=h:c|v:m|fw:b|bc:FCE4D6]NT[/XD][/XR][XR][XD=ch:15]1[/XD][XD=h:l]F/B MM[/XD][XD=h:c]33.2[/XD][XD=h:c]22.57[/XD][/XR][XR][XD=ch:15]2[/XD][XD=h:l]F/B MM 2[/XD][XD=h:c]7.98[/XD][XD=h:c]5.42[/XD][/XR][XR][XD=ch:15]3[/XD][XD=h:l]F/B MM 3[/XD][XD=h:c]4.44[/XD][XD=h:c]3.02[/XD][/XR][XR][XD=ch:15]4[/XD][XD=h:l]F/B JOSHUA MARI 88[/XD][XD=h:c]40.07[/XD][XD=h:c]27.24[/XD][/XR][XR][XD=ch:15]5[/XD][XD=h:l]F/B JOSHUA MARI 9[/XD][XD=h:c]8.42[/XD][XD=h:c]5.72[/XD][/XR][XR][XD=ch:15]6[/XD][XD=h:l]F/B JOSHUA MARI 7[/XD][XD=h:c]38.95[/XD][XD=h:c]26.5[/XD][/XR][XR][XD=ch:15]7[/XD][XD=h:l]F/B JOSHUA MARI 14[/XD][XD=h:c]11.99[/XD][XD=h:c]8.15[/XD][/XR][XR][XD=ch:15]8[/XD][XD=h:l]F/B JOSHUA MARI 6[/XD][XD=h:c]6.02[/XD][XD=h:c]4.08[/XD][/XR][XR][XD=ch:15]9[/XD][XD=h:l]F/B JOSHUA MARI 2[/XD][XD=h:c]9.61[/XD][XD=h:c]6.54[/XD][/XR][XR][XD=ch:15]10[/XD][XD=h:l]F/B JOSHUA MARI 3[/XD][XD=h:c]10.82[/XD][XD=h:c]7.35[/XD][/XR][XR][XD=ch:15]11[/XD][XD=h:l]F/B JOSHUA MARI IV[/XD][XD=h:c]8.01[/XD][XD=h:c]5.44[/XD][/XR][XR][XD=ch:15]12[/XD][XD=h:l]F/B JOSHUA MARI 12[/XD][XD=h:c]12.09[/XD][XD=h:c]8.22[/XD][/XR][/RANGE]

Private Sub cmdSearch_Click()
 
    Dim VesselName As String
    Dim lastRow As Long
    Dim i As Long

    VesselName = UCase(txtSearch.Text) ' Convert txtSearch value to uppercase
    lastRow = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To lastRow
        If UCase(Worksheets("Data").Cells(i, 1).Value) Like VesselName & "*" Then ' Compare in uppercase
            txtVName.Text = Worksheets("Data").Cells(i, 2).Value
            txtGT.Text = Worksheets("Data").Cells(i, 3).Value
            txtNT.Text = Worksheets("Data").Cells(i, 4).Value
            Exit For
        End If
    Next i

    If i > lastRow Then
        MsgBox "No results found for '" & txtSearch.Value & "'.", vbInformation, "Search Results"
    End If
  
     
End Sub

 

[James006]

Hi,
Since you have already created a very nice UserForm, would recommend to see:

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.

www.myonlinetraininghub.com

 

[Mario_R]

Everytime I click the search button it says no result found for "f/b" did i missed something in the code? Thanks

Hi all
You are making two mistakes:

a) You are looking in column A when you should be looking in column B.
b) And what you write in 'txtSearch' its code looks for it as the only content of the cell when that data is a part of the cell.

Do you understand what is happening to you?

 

[Jimmypop]

Hi there

If your code is not finding all matches, there could be a couple of reasons why this is happening. Here are a few possible causes and some suggested solutions:

1. Case sensitivity: Your code might not be finding all matches because it is case sensitive, meaning it will only find matches that have the exact same capitalization as the search term. To make the search case-insensitive, you can use the StrComp function with the vbTextCompare option to compare the values of the search term and the cell value. For example:
   

   For i = 2 To lastRow
       If StrComp(Worksheets("Data").Cells(i, 1).Value, VesselName, vbTextCompare) = 0 Then
           ' code to display the match
       End If
   Next

2. Hidden characters: Sometimes there may be hidden characters in the data that prevent the code from finding all matches. You can try using the Trim function to remove any leading or trailing spaces from the search term and the cell value. For example:
   

   For i = 2 To lastRow
       If StrComp(Trim(Worksheets("Data").Cells(i, 1).Value), Trim(VesselName), vbTextCompare) = 0 Then
           ' code to display the match
       End If
   Next

3. Data format: Another possibility is that the data is not formatted consistently, which may prevent the code from finding all matches. For example, if the data in column A contains leading or trailing spaces, the code might not find matches because the search term doesn't match the data exactly. You can try formatting the data to make it consistent, or modify the code to remove any leading or trailing spaces from the data before searching for matches.

If none of these solutions work, you may need to provide more details about the specific data and search term you are using, as well as the expected results, so that we can better understand the issue and provide more targeted guidance.

You can combine the suggestions I provided into the code you provided earlier to make it more robust and accurate. Here's an example of how you can modify your code to include the case-insensitive and leading/trailing space removal suggestions:

Private Sub cmdSearch_Click()
   
    Dim VesselName As String
    Dim found As Boolean
    
    VesselName = Trim(txtSearch.Text) ' Remove leading/trailing spaces from search term
    lastRow = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
    found = False
    
    For i = 2 To lastRow
        If StrComp(Trim(Worksheets("Data").Cells(i, 1).Value), VesselName, vbTextCompare) = 0 Then ' Compare search term and cell value, ignoring case and leading/trailing spaces
            txtVName.Text = Worksheets("Data").Cells(i, 2).Value
            txtGT.Text = Worksheets("Data").Cells(i, 3).Value
            txtNT.Text = Worksheets("Data").Cells(i, 4).Value
            found = True
        End If
    Next
    
    If Not found Then
        MsgBox "No results found for '" & VesselName & "'.", vbInformation, "Search Results" ' Display the original search term without leading/trailing spaces
    End If
    
End Sub

By adding the Trim function and the StrComp function with the vbTextCompare option, the code will now remove any leading/trailing spaces from the search term and the cell values, and compare them while ignoring case. This should make the search more robust and accurate.
Note that the message box that displays the "No results found" message now displays the original search term without leading/trailing spaces, since we removed them earlier. This ensures that the user sees the exact search term they entered, even if it contained extra spaces.

 

[xainthowell]

Hi,
Since you have already created a very nice UserForm, would recommend to see:

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.

www.myonlinetraininghub.com

Sure James006 i will checked it later. Thanks