Search and replace (formula is too long)

[smaon]

Version: Excel 2002

I have a very large spreadsheet with most cells containing very long paragraphs (nature of the legal field). I am having to search and replace a particular word/name (e.g., search Kim and replace it with Sammi), but I receive a "Formula is too long" message.

I have tried the following macro, however, it appears to stop searching and replacing at some point. That is, there were several Kim names that were not replaced with Sammi. Any ideas?

Sub TextSearch()

Dim i As Integer

For i = 1 To 256  'column i = 256
    ActiveSheet.Columns(i).Replace "Kim", "Sammi"
Next

End Sub

 

[erik.van.geit]

smaon,

sorry: I followed the thread in detail but this time I forgot what you said
would this do?
it's a simple find function but with an error trap
if the cell is not changed then it will be colored

kind regards,
Erik

Sub replace_and_mark_errors()
Dim word1 As String, word2 As String
Dim cell As Range
word1 = "Kim"
word2 = "Sammi"

On Error Resume Next
With ActiveSheet.UsedRange
    Set cell = .Find(word1, LookIn:=xlValues)
    If Not cell Is Nothing Then
        Do
          temp = cell.Value
          cell.Replace what:=word1, Replacement:=word2
          If cell.Value = temp Then cell.Interior.ColorIndex = 6
          currentAddress = cell.Address
          Set cell = .FindNext(cell)
          If currentAddress = cell.Address Then Exit Do
        Loop While Not cell Is Nothing
    End If
End With
On Error GoTo 0
End Sub

 

[smaon]

Oooh, very good! I like that it shades the cell if there is an error, but it's stuck in a loop. Don't know how to stop it.

 

[erik.van.geit]

Press Escape then continue with F8.
Insert Msgbox cell.Address to see what happens

I had the loop too first, but thought it was fixed with this
currentAddress = cell.Address
Set cell = .FindNext(cell)
If currentAddress = cell.Address Then Exit Do
I don't see why this wouldn't stop the loop

if you can't fix it please post some sample data to my emailaddress

kind regards,
Erik

 

[erik.van.geit]

Sammi,

Thank you for the sample data.
This code will do the trick (it did on your workbook here)
Erroring cells will be colored yellow. An errorlist is made. Presuming you've a sheet called "errors".
It is making hyperlinks to the errors.

kind regards,
Erik

Sub replace_and_mark_errors()
Dim word1 As String, word2 As String
Dim cell As Range
Dim sh As Worksheet

word1 = "Kim"
word2 = "Sammi"
Set sh = ActiveSheet

Application.ScreenUpdating = False
Sheets("errors").Activate
Cells.Clear
Range("A1") = "replaced :" & word1
Range("A2") = "with :" & word2
Range("A3") = "errors on sheet " & """" & sh.Name & """"

sh.Activate
On Error Resume Next
With ActiveSheet.UsedRange
    Set cell = .Find(word1, LookIn:=xlValues)
    If Not cell Is Nothing Then
    firstadress = cell.Address
        Do
          temp = cell.Value
          cell.Replace what:=word1, Replacement:=word2
          If cell.Value = temp Then
            cell.Interior.ColorIndex = 6
            If FirstErrorAddress = "" Then FirstErrorAddress = cell.Address
            Application.Goto Reference:=Sheets("errors").Range("A65536").End(xlUp).Offset(1, 0)
            ActiveCell = Replace(cell.Address, "$", "")
            ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, Address:="", _
            SubAddress:=sh.Name & "!" & ActiveCell, TextToDisplay:=ActiveCell.Value
            sh.Activate
          End If
          Set cell = .FindNext(cell)
          If FirstErrorAddress = cell.Address Then Exit Do
        Loop While Not cell Is Nothing And firstadress <> cell.Address And FirstErrorAddress <> cell.Address
    End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

 

[smaon]

It works beautifully!! I ran it on the 45+ page worksheet that I actually was having problems with.

Thank you so much Erik for your assistance and persistence.

 

[rossboss]

Hi there,

This has been a really relevant thread, although now very old.

I need to do a find and replace using a macro/VBA for the same reason, I get 'formula is too long'.

Using erik.van.geit code I get an error but no replacement. Can any1 help?

Best,
Ross