Look for closest value in column from value in input field or TextBox

[ipbr21054]

Hi.

I am open to suggestion but i have this below at present to get me started.

I click on my command button & enter a value in the input field.
Example of input field value would be SHHCH1590XU201591
Now the code should look in column A4:A & advise me of the first nearest match.
Either MsgBox Your Nearest matc is at Row 55

Chances of the exact value being there is slim BUT possible hence nearest match.

If it is easier for you there is TextBox 1 on the worksheet & also a command button.
So maybe enter value in TextBox1 press command button & nearest match would turn that cell in column A red ??

I have the code so far in place but get a RTE 2004 each time so now stuck

Private Sub CommandButton1_Click()
   Dim ans As String
   Dim Fnd As Range
   Do Until Not Fnd Is Nothing
      ans = InputBox("Enter search for value")
      If ans = "" Then Exit Sub
      Set Fnd = Range("A4:A").Find(ans, , , xlPart, , , False, , False)
   Loop
End Sub

 

[ipbr21054]

Nice looking user form.
would you mind uploading that please or advise the grey uses thanks.

 

[EXCEL MAX]

This is the basic example.
Now you got many easy accessible properties of the input box.
Sorry, but design is not up to me.

 

[ipbr21054]

Now installed and will try it for a day or two thanks

 

[ipbr21054]

Hi,
Just playing with this search code & noticed something.

These are the values i have on the worksheet.

If i search for LUCGF i am told row 182 is the closest match.
Would you say that's incorrect & Row 186 is the closest match or am i wrong ?

I ask because if there were say 20 rows starting LUCGE then do you see where im coming from.

 

[EXCEL MAX]

The code search the range from the top to the bottom and say that nearest value is
the first value that include parameter in the search TextBox as part of the cell text.
If this parameter not exist in any cell,
code search for first value from top to the bottom with parameter from TextBox without last character.
Why do you think that cell 186 is nearest then 182?
Depends what you think that is close match.
It's hard to detect nearest value if you don't know what pattern you think is the best.
Did your data appears in some pattern?

 

[ipbr21054]

I understand what you are saying so will use what you have provided.

Many thanks

 

[EXCEL MAX]

It's my pleasure...

 

[ipbr21054]

@EXCEL MAX
This is the code in use,
Can you edit / advise please how after typing the value pressing ENTER will run the code, Thus me not having to click the OK button

Private Sub VinToolLookUp_Click()
    Range("A4").Select
    Dim ans As String
    Dim Fnd As Range
    Dim varNRows As Long
    Dim varFndRow As Long

    varNRows = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A4:" & "A" & varNRows).Interior.Color = vbGreen
    HondaListVinForm.Show
    ans = HondaListVinForm.TextBox1.Text
    HondaListVinForm.TextBox1.Text = ""
    HondaListVinForm.TextBox1.SetFocus
    If ans = "" Then Exit Sub
EX:
    Set Fnd = Range("A4:A" & varNRows).Find _
        (ans, Range("A" & varNRows), , xlPart, , , False, , False)
    If Not Fnd Is Nothing Then
        varFndRow = Fnd.Row
        Cells(varFndRow, "A").Interior.Color = RGB(51, 255, 255)
        MsgBox "THE NEAREST MATCH IS AT ROW " & varFndRow, vbInformation
        Cells(varFndRow, "A").Select
    Else
        ans = Left(ans, Len(ans) - 1)
        GoTo EX
    End If
    
    End Sub

 

[EXCEL MAX]

Move your procedure to the standard module and call it with Textbox1 KeyDown event.
Use this...

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

   If KeyCode = 13 Then VinToolLookUp

End Sub