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

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,048
Office Version
  1. 2007
Platform
  1. Windows
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

Rich (BB code):
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

Well-known Member
Joined
Nov 16, 2010
Messages
4,048
Office Version
  1. 2007
Platform
  1. Windows
Nice looking user form.
would you mind uploading that please or advise the grey uses thanks.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
418
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
4,048
Office Version
  1. 2007
Platform
  1. Windows
Now installed and will try it for a day or two thanks
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,048
Office Version
  1. 2007
Platform
  1. Windows
Hi,
Just playing with this search code & noticed something.

These are the values i have on the worksheet.

1610473358308.png


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

Active Member
Joined
Nov 11, 2020
Messages
418
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Nov 16, 2010
Messages
4,048
Office Version
  1. 2007
Platform
  1. Windows
I understand what you are saying so will use what you have provided.

Many thanks
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
418
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

It's my pleasure...
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,048
Office Version
  1. 2007
Platform
  1. Windows
@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


Rich (BB code):
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

Active Member
Joined
Nov 11, 2020
Messages
418
Office Version
  1. 2016
Platform
  1. Windows
Move your procedure to the standard module and call it with Textbox1 KeyDown event.
Use this...
VBA Code:
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

   If KeyCode = 13 Then VinToolLookUp

End Sub
 

Forum statistics

Threads
1,136,909
Messages
5,678,520
Members
419,768
Latest member
eguechi09x

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top