Find Insert Multiple Occurences

[excel5*user]

I need help with an Excel Macro. I need to find each occurence of 24 different TEXTs and next to each insert cells to add additional information. The number of occurences of each text varies and may appear in the range of K2:IV2000, so there may be for example 14 occurences of TEXT1 and then 35 occurences of TEXT2, and so on.

I need to find TEXT1 (ie. A1234) and insert a cell next to it, add text1a ( ie.B1234), and do this for all occurences of TEXT1. Then I need it to find TEXT2, insert a cell next to it and add text2a, and repeat for all occurences of TEXT2. etc for all 24 different TEXT. The find and insert part of the macro works, but I cannot get it to repeat correctly. I can only get it to find one occurence of each and when trying looping, the loop just finds the next TEXT whether it is TEXT1, TEXT2, TEXT3, etc. and performs the insert leaving several instances of each of the TEXTs skipped/not processed.

Thanks

 

[balajayan]

You can try the following macro

Sub findandreplacetext()
replacetxt = Array("TEXT1a", "TEXT2a", "TEXT3a", "TEXT4a", "TEXT5a", "TEXT6a", "TEXT7a", "TEXT8a", "TEXT9a", "TEXT10a", _
                    "TEXT11a", "TEXT12a", "TEXT13a", "TEXT14a", "TEXT15a", "TEXT16a", "TEXT17a", "TEXT18a", "TEXT19a", "TEXT20a", _
                    "TEXT21a", "TEXT22a", "TEXT23a", "TEXT24a")
findtext = Array("TEXT1", "TEXT2", "TEXT3", "TEXT4", "TEXT5", "TEXT6", "TEXT7", "TEXT8", "TEXT9", "TEXT10", _
                    "TEXT11", "TEXT12", "TEXT13", "TEXT14", "TEXT15", "TEXT16", "TEXT17", "TEXT18", "TEXT19", "TEXT20", _
                    "TEXT21", "TEXT22", "TEXT23", "TEXT24")
For i = 0 To 23
    For colcu = 11 To 256
        For rowcu = 2 To 2000
            If Cells(rowcu, colcu) = findtext(i) Then
                Cells(rowcu, colcu + 1).Insert (xlShiftToRight)
                Cells(rowcu, colcu + 1).Value = replacetxt(i)
            End If
        Next
    Next
Next
End Sub

 

[Weaver]

Wouldn't it be easier to build the arrays in a sheet, rather than in Array statements?

 

[balajayan]

If array values are there in excel, say in column A2 to A25 has TEXTs available in data & B2 to B25 are TEXT to be inserted, then can use the following code

For i = 0 to 23
findtext(i) = cells(i+2,1).value
replacetxt(i) = cells(i+2,2).value
Next

instead of

replacetxt = Array("TEXT1a", "TEXT2a", "TEXT3a", "TEXT4a", "TEXT5a", "TEXT6a", "TEXT7a", "TEXT8a", "TEXT9a", "TEXT10a", _
                    "TEXT11a", "TEXT12a", "TEXT13a", "TEXT14a", "TEXT15a", "TEXT16a", "TEXT17a", "TEXT18a", "TEXT19a", "TEXT20a", _
                    "TEXT21a", "TEXT22a", "TEXT23a", "TEXT24a")
findtext = Array("TEXT1", "TEXT2", "TEXT3", "TEXT4", "TEXT5", "TEXT6", "TEXT7", "TEXT8", "TEXT9", "TEXT10", _
                    "TEXT11", "TEXT12", "TEXT13", "TEXT14", "TEXT15", "TEXT16", "TEXT17", "TEXT18", "TEXT19", "TEXT20", _
                    "TEXT21", "TEXT22", "TEXT23", "TEXT24")

 

[excel5*user]

The array works, THANKS! Another question though. In some cases I need to insert just one cell of text#a next to TEXT#, and sometimes there may be as many as five additional texts that need to be added.

For example, TEXT1 needs text1a added next to it, but TEXT2 needs text2a, text2b, text2c, and then TEXT3 needs text3a and text3b. etc How would I incorporate that.

Thanks

 

[excel5*user]

Nevermind, I just answered my own question, I just need to add additional copies of the macro for multiple text.

Thanks again!