Best way to search for a sub string

  • Hi all,
    I need code in a macro to search a variable string called Tit in column A of a sheet and if found get the row number.
    My search should NOT be case sensitive and NOT exact match. I can use Instr function but then I have to loop through 3000 lines.


    I have tried using wild cards:


    Tit = range("B1")
    Set Rng = Range("A1:A3000")
    If Application.WorksheetFunction.CountIf(Rng, "*" & Tit & "*") > 0 Then
    Row = Application.WorksheetFunction.Match(Tit, Rng, 0) (or Rv = Application.WorksheetFunction.Match("*" & Titolo "*", Rng, 0)
    Else: End if


    But no luck.


    Appreciate your help

  • Hi,


    You could test following macro to find your string ...



    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Thank you Carim for your kind and prompt reply.


    I tried your code, however, it does not work as I hoped.


    Example:


    Tit= "Poste Italiane/ao"
    and in A10 I have: Poste Italiaeaor Milano


    The code will not give me a match.
    That's why I thought about wild cards in my first post.

  • You will need some way to identify how much of "B1" you want to check for.


    Code
    Dim vCharacter
    
    
    Tit = Range("B1").Value
    vCharacter = Range("A1").Value
    Tit = Left(Tit, vCharacter)


    'vCharacter = 12' will display your example.


    However, if you are trying to use a single cell, to search multiple strings using a special character like "/" to split them, that it going to be tough since there are so many possibilities. You would have an easier time of making seperate cells, or a range, and searching for these using Carim's code with a slight adjustment to check for multiple Tits.


    Code
    Tit1 = "Poste Italiane"
    Tit2 = "Poste Italiaea"

  • Hi,


    Have you tried to use what:=Left(Tit,12) instead of what:=Tit


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Thank you all for your kind help.


    I finally succeeded by:


    1. Cleaning the lookup range (A1:A3000) by substituting all special characters with a space.
    2. Choosing the left most word in the text I was searching for. I did not use the suggested Left(Tit, 12) because Tit was dynamic and the first word could have had any length.
    3. Used the Find method to search my lookup range.


    It works fine and no looping.

  • Hello,


    Glad you could fix your problem ...:wink:


    Thanks ... for your your Thanks ... AND for your click on the Like icon ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!