Searching for similar strings and display it on combo box

xboon_95

Board Regular
Joined
Jun 18, 2014
Messages
53
Hi all, I'm trying to search for similar strings and display it on combo box. For instance, my data is as shown below :

FW version: 4.03x(22)
PWM (IF/OF): 230/156
SP (IF/mmHg): 415/495
SP (OF/mmHg): -91/-120
SO time (OF to IF/sec): 35
SO time (IF to OF/sec): 55

I'm trying to search for the string "FW version" and display the above data out. I couldn't use the search function to search for "FW version: 4.03x(22)" as the FW version differs from data to data. Some of my data might be:

FW version: 4.54x(21)
PWM (IF/OF): 260/160
SP (IF/mmHg): 495/995
SP (OF/mmHg): -98/-140
SO time (OF to IF/sec): 35
SO time (IF to OF/sec): 55

or


FW version: 4.67x(24)
PWM (IF/OF): 220/150
SP (IF/mmHg): 475/399
SP (OF/mmHg): -99/-110
SO time (OF to IF/sec): 45
SO time (IF to OF/sec): 25



Is there any way to display that out? I went onto the net and found something called "fuzzylookup" but I'm not sure how to execute it. Any help here?


Code:
Private Sub CommandButton6_Click()
On Error GoTo Errhandler
ComboBox4.TextAlign = fmTextAlignCenter


Dim SearchRange As Range
    Dim FindRow As Range
    Set SearchRange = Range("A1", Range("A65536").End(xlUp))
    Set FindRow = SearchRange.Find("FW version: 4.03x(22)", LookIn:=xlValues, LookAt:=xlWhole)
    Dim TheSheet As Worksheet
    Set TheSheet = ActiveSheet


row_review = FindRow.Row


    Do
    DoEvents
    row_review = row_review + 1
    item_in_review = TheSheet.Range("A" & row_review)
     
    If Len(item_in_review) > 0 Then ComboBox4.AddItem (item_in_review)
    
    Loop Until item_in_review = ""
    
    MsgBox "Complete"
    
Exit Sub
    
Errhandler:


MsgBox "Please kindly load your file, thank you."
End Sub
 
Last edited:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Hi,
using FindNext in your code (an example can be found in VBA helpfile) with LookAt: set to xlPart may do what you want.

See if this update to your code helps:

Code:
Private Sub CommandButton6_Click()
    Dim FirstAddress As String, Search As String
    Dim FindRow As Range
    Dim TheSheet As Worksheet


    Set TheSheet = ActiveSheet
    
    Search = "FW version:"


    With TheSheet
        Set FindRow = .Columns(1).Find(Search, _
                                       LookIn:=xlValues, _
                                       LookAt:=xlPart, _
                                       SearchOrder:=xlByRows, _
                                       SearchDirection:=xlNext, _
                                       MatchCase:=False, _
                                       SearchFormat:=False)


        If Not FindRow Is Nothing Then
            FirstAddress = FindRow.Address
            Me.ComboBox4.Clear
            Do
                Me.ComboBox4.AddItem Mid(FindRow.Value, 12)
                Set FindRow = .Columns(1).FindNext(FindRow)
            Loop While Not FindRow Is Nothing And FindRow.Address <> FirstAddress
        End If
    End With


End Sub

Dave
 
Upvote 0
Hi,
using FindNext in your code (an example can be found in VBA helpfile) with LookAt: set to xlPart may do what you want.

See if this update to your code helps:

Code:
Private Sub CommandButton6_Click()
    Dim FirstAddress As String, Search As String
    Dim FindRow As Range
    Dim TheSheet As Worksheet


    Set TheSheet = ActiveSheet
    
    Search = "FW version:"


    With TheSheet
        Set FindRow = .Columns(1).Find(Search, _
                                       LookIn:=xlValues, _
                                       LookAt:=xlPart, _
                                       SearchOrder:=xlByRows, _
                                       SearchDirection:=xlNext, _
                                       MatchCase:=False, _
                                       SearchFormat:=False)


        If Not FindRow Is Nothing Then
            FirstAddress = FindRow.Address
            Me.ComboBox4.Clear
            Do
                Me.ComboBox4.AddItem Mid(FindRow.Value, 12)
                Set FindRow = .Columns(1).FindNext(FindRow)
            Loop While Not FindRow Is Nothing And FindRow.Address <> FirstAddress
        End If
    End With


End Sub

Dave

Hey dude! It's working! But It's only able to display "FW version: 4.03x(22)". Can I display the rest of my data as well? For instance,

FW version: 4.03x(22)

PWM (IF/OF): 230/156
SP (IF/mmHg): 415/495
SP (OF/mmHg): -91/-120
SO time (OF to IF/sec): 35
SO time (IF to OF/sec): 55

That would be perfect :)
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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
Back
Top