CODEWITHSUNDEEP
excelreplace
D07062011
D07062011

Reputation: 9

Excel Find and Replace

My question is how can I do a replace and find with a replace word that is never the same. Basically, I would like to make a find and replace the word "x" for, let's say 5 rows with the word "A" and do a find and replace for the next five ones with the word "B" and so on for a thousand times. How do I manage to do that?

Upvotes: 0

Views: 880

Answers (2)

Charles Goodwin
Charles Goodwin

Reputation: 6652

Can you export to CSV? If so, you could do that and then use common command line tools in Linux or a text editor that you know has the features to find/replace as you have outlined.

You can use Gnumeric or LibreOffice to do this (on Linux, Windows).

Upvotes: 0

Dick Kusleika
Dick Kusleika

Reputation: 33175

Always make a backup of your data before you run code from the internet. This will find all the instances of "x" in column A and replace them with the values you set in vaReplace.

Sub FindReplace()

    Dim rFound As Range
    Dim vaReplace As Variant
    Dim lFound As Long
    Dim lRepCount As Long

    Const lMAX As Long = 5 'how many to replace before switching values
    Const sFIND As String = "x" 'what to look for
    Const lCOLUMN As Long = 1 'which column to look in

    'list of values to use as replacements
    vaReplace = Array("A", "B", "C", "D", "E")

    Set rFound = Sheet1.Columns(lCOLUMN).Find(sFIND, Sheet1.Cells(Sheet1.Rows.Count, lCOLUMN), xlValues, xlWhole)
    lFound = 1
    lRepCount = 0

    If Not rFound Is Nothing Then
        Do
            If lFound > lMAX Then
                lRepCount = lRepCount + 1
                lFound = 1
            End If

            'if not enough in vaReplace, use the last value
            If lRepCount > UBound(vaReplace) Then lRepCount = UBound(vaReplace)

            rFound.Value = vaReplace(lRepCount)

            Set rFound = Sheet1.Columns(1).FindNext

            lFound = lFound + 1

        Loop Until rFound Is Nothing
    End If

End Sub

Upvotes: 3

Related Questions