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 19 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:)

  • 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?

  • 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:)

  • 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:)