Searching for similar strings and display it on combo box

[xboon_95]

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?

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

 

[dmt32]

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:

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

 

[xboon_95]

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:

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