VBA: Find and replace an "array" of words

[FryGirl]

I found this macro which works fine for one word, but not sure how not modify for changing an array of words.

Right now the one word within the string I'm looking for is "DET:", but I'd like to add many others.

Could somebody help me with the next step?

Sub ReplaceStr()
    Dim OriVal As String, RepWhat As String, RepWith As String, TempStr As String
    Dim Cell As Range, Rng As Range
    Dim RepLen As Integer
   
    Application.ScreenUpdating = False
    Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    
    RepWhat = "DET:"
    RepWith = "Det:"
    RepLen = Len(RepWhat)

        For Each Cell In Rng
            OriVal = Cell.Value

            Do Until InStr(OriVal, RepWhat) = 0
                TempStr = TempStr & Left(OriVal, InStr(OriVal, RepWhat) - 1) & RepWith
                OriVal = Right(OriVal, Len(OriVal) - ((InStr(OriVal, RepWhat) - 1) + RepLen))
            Loop

            Cell.Value = TempStr & OriVal

            TempStr = ""
            OriVal = ""

        Next Cell
    
    Application.ScreenUpdating = True

End Sub

 

[hiker95]

FryGirl,

Good Morning Hiker95,

All is testing out great so far.

Thanks for the feedback.

You are very welcome. Glad I could help.

but this does not like spreading out vertically, but of course if I change to something like .Range("E1:U1") the array reads in correctly.

Can this be done? Reading the array onto the spreadsheet horizontally?

Yes, but it would take up more space in the worksheet.

I would think that the maintenance of the two arrays would be very time consuming. And, if you did not update the items in the two separate arrays in their respective positions, then it would not work correctly.

If makes more sense to display the Search/Replace values like you original had them.

If you did not want the information seen in the raw data worksheet, it could go into another worksheet.

 

[FryGirl]

If you did not want the information seen in the raw data worksheet, it could go into another worksheet.

I don't mind the information being seen, I was just hung up on a mental block, but you have challenged me to think it through further and I will end up using a separated worksheet to store the list.

When I pull a new query Business Objects and save the results as Excel, I will then just transfer that worksheet into the workbook with the search/replace list.

Sound like a good plan, now the testing. Thanks again, you have helped tremendously.

 

[hiker95]

FryGirl,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.