Trim the recorded macro

  • Good Day,


    Please assist to trim the recorded macro.




    A table consist of same header, I'm replacing the header names with find and replace vba code.


    Ignoring the two matches and Replacing 3rd match, 4th match, 5th match as Apple,Mango and Orange respectively.


    Kindly refer to the attached file.


    -Anna

    Files

    • U1.xlsm

      (21.33 kB, downloaded 2 times, last: )
  • Try this


    Code
    1. With ActiveSheet.UsedRange.Cells
    2. .Replace What:="Fruit", Replacement:="Apple", LookAt:=xlPart, _
    3. SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    4. ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    5. End With
    6. End Sub
  • I tried trimming and getting errors. I have attached my sample workbook in the above post. Attaching it again..

    Files

    • U1.xlsm

      (21.33 kB, downloaded 5 times, last: )
  • Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Thank you, Mumps. Really appreciate your help!! What should I update in the above code, if I require to change only 3rd and 5th instance of "Fruit"?

  • Replace this line of code:

    Code
    1. v = Array("Fruit", "Fruit", "Apple", "Mango", "Orange")

    with this line:

    Code
    1. v = Array("Fruit", "Fruit", "Apple", "Fruit", "Orange")

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.