Find and replace in sequence

[makulit]

I have a big file with a word scattered all over. For example, the word is
"TEXT".
I want to find all of it and replace it with a sequencial number attached to
it. The result should be like, TEXT1, TEXT2, TEXT3, etc. Can this be done? I
would sure like to know. I'm using excel2007

Thanks in advance.

Julius

 

[Bernard Liengme]

The VBA subroutine does what you want. It works row by row

Sub TextCount()
j = 1
For Each mycell In Range("A1:F10")
If mycell.Value = "TEXT" Then
mycell.Value = "TEXT" & j
j = j + 1
End If
Next
End Sub

New to VBA? Then see David McRitchie's site on "getting started" with VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Debra Dalgleish's "Adding Code to a Workbook"

http://www.contextures.com:80/xlvba01.html
best wishes

 

[Gary''s Student]

and if TEXT are in sentences:

Sub changeThem()
Dim s As String
s = "TEXT"
b = " "
i = 1
For Each r In ActiveSheet.UsedRange
v = r.Value
If InStr(v, s) > 0 Then
sary = Split(v, b)
For j = LBound(sary) To UBound(sary)
If sary(j) = s Then
sary(j) = sary(j) & i
i = i + 1
End If
Next
r.Value = Join(sary, b)
End If
Next
End Sub