VBA code for exact and closely matching text

  • Hi All


    I'm looking for a VBA code for exact and closely matching text.


    Please find the scenario below


    If I enter any of the regions(AD,FE,EU) in another new excel sheet, then it must extract the codes from column D closely matching along with the region name in column E

    E.g. If I enter Aalborg, then result must appear like below

    Search Criteria : Aalborg

    Results :

    Code
    1. DKAAL - Aalborg
    2. DKROR - Aalborg Portland Cementfabrikk
    3. NLALB - Aalburg
  • Hello and Welcome to the Forum :)


    Why would you need a macro ... when a simple AutoFilter can do the job ...


    see your test file attached


    Hope this will help

    :)

    Files

    • Test MAC.xlsm

      (7.57 MB, downloaded 51 times, last: )

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

  • Thank you so much, Carim for your valuable time. But the task which assigned by my employer has specified to use Macro only. Can we use Autofilter in vba code?? Please share your suggestions.

  • Carim, I have attached solution for exact matches but I couldn't figure it out for closely matches.


    For eg:


    If I enter "Zebra" in the highlighted box (Exact Image. PNG Attachment), My result should also include Zebre, Zabra, Zabre, Zebea.


    Please do let me know if any additional details are required.

  • Hello,


    For sure you know images are totally inert objects ... and by consequence TOTALLY USELESS ...:cursing::thumbdown:


    As already indicated ... attaching an Excel file is the solution ...!!!:thumbup:

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

  • Sub Textmatch()


    Sheets("FMA").Select

    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=5, Criteria1:= _

    Sheet4.Range("F4"), Operator:=xlAnd

    Range("D2:E111554").Select

    Selection.Copy

    Sheets("Approach 3").Select

    Range("B8").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

    :=False, Transpose:=False

    Range("D4:E4").Select

    Sheets("FMA").Select

    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=5

    Sheets("Approach 3").Select

    Range("D4:E4").Select

    End Sub


    Could you please tell how to declare variables in the above code?

    Files

    • FMA.xlsm

      (7.73 MB, downloaded 51 times, last: )
  • Hello,


    May be a simplified macro as follows :

    Hope this will help

    :)

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

  • Glad you could fix your problem :)


    Thanks for your Thanks ...AND for the Like :thumbup:

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

  • Oh thanks for highlighting, Carim. Do you know how to make cells not to move in excel? In other words, I need only one cell to be accessible. I have used protect sheet feature but i could switch to another cell. But i don't want even to move it. I hope you understand my question. I will be waiting for your reply, Carim. please enlighten me if its possible.

  • Hello,


    For your last question ... it is a two-step process


    1. Select the Cell and format it as UnLocked


    2. From Main Menu, select Protect Sheet


    Hope this will help

    :)

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