Replacing Strings through VBA Coding

Question

I am trying to replace the string (multiple words) of a particular column with a string (one word) contained in the array if the string (one word) in the array matches with the string (multiple words) of that cell. I have used the following code. It doesn't show any error but it is not running. So, basically, when I run this code there is no change in my data set. Can anyone kindly help me out what is the problem in this code?

  Sub FindReplace()
  Dim Arr As Variant
  Arr = ActiveSheet.Range("T2:T854")
  Dim i As Long
  For i = LBound(Arr) To UBound(Arr)
  Columns("H:H").Select
  Selection.Replace What:="*Arr(i)*", Replacement:="Arr(i)", LookAt:= _
    xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
  Next i

  End Sub

My data has H column containing these kind of queries (approx 90000): tour packages for Bangladesh, Afghanistan activities, tour packages for Bangladesh, romantic places in Bali, tour packages for Bangladesh, tour packages for Bangladesh, tour packages for Bangladesh, weather in Algeria, tour packages for Bangladesh, tour packages for Bahamas, tour packages for Bahamas, Bangladesh visitor visa, tour packages for Algeria, tour packages for Bahamas, tour packages for Algeria and the T column contains names of approx 1000 destinations like Bangladesh, Algeria,etc. @Jeeped — Dynamo
– Dynamo, Commented Jul 17, 2018 at 22:14
I need to replace the queries in H column just by the destination names if a word in any cell of H column matches with the destination names present in T column. @Jeeped — Dynamo
– Dynamo, Commented Jul 17, 2018 at 22:18
Let me explain you with an example: for a string containing 'Bangladesh visitor visa' I want to replace it with just 'Bangladesh' , for 'tour packages for Algeria' I just want to replace it with 'Algeria'... and so on! @Jeeped — Dynamo
– Dynamo, Commented Jul 17, 2018 at 23:34

score 1 · Accepted Answer · 2018-07-18 02:49:02Z

1

I believe you just have some string concatenation issues but some code could be cleaned up.

Sub FindReplace()
    Dim Arr As Variant, i As Long

    with ActiveSheet
        Arr = .Range(.Cells(2, "T"), .Cells(.Rows.Count, "T").End(xlUp)).value2

        with .columns(8)
            For i = LBound(Arr, 1) To UBound(Arr, 1)
                .Replace What:="*" & Arr(i, 1) & "*", Replacement:=Arr(i, 1), _
                         LookAt:=xlwhole, SearchOrder:=xlByRows, MatchCase:=False, _
                         SearchFormat:=False, ReplaceFormat:=False
            next i
        end with
    end with

End Sub

Collectives™ on Stack Overflow

Replacing Strings through VBA Coding

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related