• Question

  • Hi please help me I'm so noob in VBA. We had a project given to us by our professor. he wants us to search a category for PRIME NO. RFQ Category  and description. the he gives us in the code below just scroll down . for example if I Search a category  for LOS ANGELES LAKERS. I will start searching for the whole word which is the LOS ANGELES LAKERS then if I couldn't find LOS ANGELES LAKERS in the category I will search for the word ANGELES if I couldn't find again the category next is the LOS . if ever nothing happened again I will search again but this time I will start from Los To Angeles to Lakers. please I need you answer code please. thanks

    Wednesday, July 19, 2017 9:45 AM

All replies

  • Not sure if the following is really what you are after. The screen snippet shows the data to be searched and you will see that I changed the words around for Los Angeles Lakers to establish that it can find by individual words in the original text to find.

    The code first searches for the entire string and if not found then it uses an array of the words in the string and then searches for the individual words. At this point in time it does not perform find next but lets see if I am on the correct path for what you want before I move to find Next (if it is required).

    Code below for the search.

    Sub Search()
        Dim ws As Worksheet
        Dim rngToSearch As Range
        Dim rngToFind As Range
        Dim strToFind As String
        Dim arrSplit As Variant
        Dim i As Long
        Dim j As Long
        Set ws = Worksheets("Sheet1")       'Edit "Sheet1"to your worksheet name
        With ws
            Set rngToSearch = .Range(.Cells(5, "A"), .Cells(.Rows.Count, "A").End(xlUp))
            strToFind = .Cells(2, "A").Value        'Commence with full string. Edit "A" to required column
            arrSplit = Split(strToFind, " ")      'Split at spaces and assign to array
        End With
            Set rngToFind = rngSearch(rngToSearch, strToFind)
                If Not rngToFind Is Nothing Then        'Not Nothing then is something so found
                    Application.Goto rngToFind          'Select the cell containing the found data
                    Exit Do
                Else    'If not found then use the array of strings
                    i = i + j   'First iteration will be zero
                    If i > UBound(arrSplit) Then    'Past end of array
                        MsgBox strToFind & " not found."
                        Exit Do
                    End If
                    strToFind = arrSplit(i)
                    j = j + 1
                End If
    End Sub

    Function rngSearch(rng As Range, str As String) As Range
        Dim rngToFind As Range
        With rng
            Set rngSearch = .Find(What:=str, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlFormulas, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False, _
        End With
    End Function

    Regards, OssieMac

    Monday, July 24, 2017 2:21 AM