+ Reply to Thread
Results 1 to 19 of 19

Search for Keywords and Return the Appropriate Item

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: Search for Keywords and Return the Appropriate Item

    I am too slow again.. but will post my code to see if I was on the right track..

    I took a different approach.. and made it show multiple matches separated by a "/".

    For example:
    Samsung R351
    showed (in column M)
    Samsung R351 / SAMSUNG LINK SOLO R351 / SAMSUNG LINK R351

    Private Sub CommandButton2_Click()
        Dim arr, SS1
        Dim S1 As String, S2 As String, SArray As String, Response1 As String
        Dim LastRowAdd As String, FirstRowAdd As String
        Dim LastRow As Long, FirstRow As Long, LastRowSearch As Long
        Dim i As Long, j As Long, c As Long
        Application.ScreenUpdating = False
        For j = 4 To Range("L" & Rows.Count).End(xlUp).Row
            SS1 = Split(Cells(j, 12).Value, " ")
            S1 = SS1(0)
            S2 = SS1(1)
            Sheets("Item List").Range("A1:M" & Sheets("Item List").Range("A" & Rows.Count).End(xlUp).Row).AutoFilter 2, "*" & S1 & "*"
            FirstRowAdd = FirstVisibleValue(ActiveSheet, 1)
            FirstRow = Range(FirstRowAdd).Row
            LastRow = Sheets("Item List").Range("A" & Rows.Count).End(xlUp).Row
            LastRowAdd = Sheets("Item List").Cells(LastRow, "A").Address
            If Sheets("Item List").Range(FirstRowAdd).Value = "" Then GoTo NotFound
            For i = FirstRow To LastRow
                c = 0
                SArray = Sheets("Item List").Cells(i, 1).Value
                arr = Split(SArray, ",")
                If S1 = "" Then Exit Sub
                Response1 = IsInArray(S2, arr)
                If Response1 = "True" Then
                    c = 1
                    cntS2 = cntS2 + c
                End If
                If c = 1 And cntS2 = 1 Then
                    Cells(j, 13).Value = Sheets("Item List").Cells(i, 1).Value
                ElseIf c = 1 And cntS2 > 1 Then
                    Cells(j, 13).Value = Cells(j, 13).Value & " / " & Sheets("Item List").Cells(i, 1).Value
                End If
    NotFound:
            Next i
            cntS2 = 0
        Next j
        Application.ScreenUpdating = True
    End Sub
    In a Module:
    Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
      IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
    End Function
    
    
    Function FirstVisibleValue(ByRef Sht As Worksheet, ByVal FilterCol As Long)
        Dim R As Range
        If Sheets("Item List").AutoFilterMode Then
            Set R = Sheets("Item List").AutoFilter.Range
            FirstVisibleValue = R.Offset(1, FilterCol - 1).Resize(R.Rows.Count, 1).SpecialCells(12).Cells(1).Address
        End If
    End Function
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Search for keywords and copy rows containing keywords to new sheet
    By lenorsk in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-15-2013, 06:54 AM
  2. [SOLVED] HDI - Return multiple keywords in a text column based on a set of available keywords
    By zneiley in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-25-2013, 01:32 PM
  3. to return Mutliple results with one search item
    By Bald Ben in forum Excel General
    Replies: 2
    Last Post: 01-14-2011, 04:44 PM
  4. Replies: 12
    Last Post: 10-01-2009, 01:05 PM
  5. Replies: 2
    Last Post: 10-24-2008, 08:36 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1