Search User Input String in variants of string - find all in any order contained

  • Hi all,


    First post - so hello one and all.


    I am stuck with using a user input search term in a cell, and finding variants of this string.


    Cell Entry Example: Professional Land Fees


    I want to then find all cells that contain all of those words in all orders (eg Professional Land Fees / Land Professional Fees / Fees Land Professional / Land Fees Professional) - I am able to search and then carry out actions when the string is found in the order of user entry, I am struggling with coding it to look for all variants.


    Any help very much appreciated.


    Thank you in Advance.


    Code
    1. For Each Cell In SearchRange
    2. If InStr(Cell, Findvalue) > 0 Then
    3. MsgBoxStr = MsgBoxStr & Cell.Offset(0, 3).Value & " - " & Cell.Offset(0, 4).Value & vbCr
    4. End If
    5. Next Cell
  • Re: Search User Input String in variants of string - find all in any order contained


    One way

  • Re: Search User Input String in variants of string - find all in any order contained


    try this


  • Re: Search User Input String in variants of string - find all in any order contained


    Hi,


    I looked at that previously, however the cell entry of user input is variable. So, it can be 1 word, or 4 words - I am trying to locate all words within a cell (no matter what order). I have tried SPLIT, and building up muti strings with JOIN, but to no avail. I am struggling with the flexibility of multiple words and arrangement.


    Thank you both for your help....

  • Re: Search User Input String in variants of string - find all in any order contained


  • Re: Search User Input String in variants of string - find all in any order contained


    Thanks, although this is a little beyond my understanding, and I am having issues in converting your code to my use.


    My search range and search string are set and entered into your code as:

    Code
    1. Dim Findvalue As String
    2. Dim SearchRange As Range
    3. Set SearchRange = Sheets("Cost_Heads").Range("B8:B200")
    4. Findvalue = Sheets("Mapping_LookUp_Tool").Range("E12").Value
    5. Set AL = CreateObject("System.Collections.ArrayList")
    6. For Each e In Split(Findvalue, " ")
    7. AL.Add StrConv(e, vbProperCase)
    8. Next


    However I am then stuck on the rest in looping through each cell in the searchrange to find all words......


    Sorry for being spoon fed....

  • Re: Search User Input String in variants of string - find all in any order contained


    If Mapping_LookUp_Tool!E12 contains one or multiple string separated by single space then this code should work.
    Otherwise need to see your workbook.

  • Re: Search User Input String in variants of string - find all in any order contained


    Thanks for the help and responses. I ended up with the following:


  • Re: Search User Input String in variants of string - find all in any order contained


    Hope you can use this as a UDF. Instead of "OK" in the code you can use, for example, the original user input (now: Professional Land Fees)


    Function FindPerm(c As String) As String
    With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "(Professional)|(Land)|(Fees)" 'Insert more words with similar structure
    FindPerm = .Replace(c, "#")
    .Pattern = "^# # #$" 'Insert more # with space
    If .Test(FindPerm) Then
    FindPerm = .Replace(FindPerm, "OK")
    Else
    FindPerm = ""
    End If
    End With
    End Function