While using find and replace i am getting "formula too long"

[Guest]

Hi

I am working in excel.
we have description are more in one cell. Like that we will be having
thousands of cells. Wherein we need to have some changes. So that i use Find
and replace(Ctrl+H) to replace some changes. While using that i am getting
for some perticular cell "Formula Too Long". Please give me an suggession
how to resolve this.

Expecting your early reply.

venkat

 

[Guest]

Not knowing what you are searching for, not what you are replacing this with,
makes it impossible to help you!

 

[Dave Peterson]

You can use code to do as many as possible, then loop through each cell that
complained.

This may give you a starting point. I save it from a previous post:

Option Explicit
Sub testme01()

Dim FoundCell As Range
Dim ConstCells As Range
Dim BeforeStr As String
Dim AfterStr As String

BeforeStr = ",,"
AfterStr = ","

With ActiveSheet
Set ConstCells = Nothing
On Error Resume Next
Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _
xlTextValues)
On Error GoTo 0

If ConstCells Is Nothing Then
MsgBox "Select some cells in the used range"
Exit Sub
End If

With ConstCells
'get as many as we can in one step
.Replace what:=BeforeStr, Replacement:=AfterStr, _
lookat:=xlPart, SearchOrder:=xlByRows

Do
Set FoundCell = .Cells.Find(what:=BeforeStr, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlPart, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'done, get out!
Exit Do
End If
FoundCell.Value _
= Replace(FoundCell.Value, BeforeStr, AfterStr)
Loop
End With
End With
End Sub

If you're using xl97, change that Replace() to application.substitute()

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ps. Try it against a copy of your data--just in case!

 

[The Moose]

Dave,

I saw this macro and thought it might have some application in what I'm
doing.

I have a spreadsheet that I pull down from a website just about every
day. It's got a lot of typos and data entry errors. I've pretty much
tracked every type of error -- there's about 50 different ones.

How would I modify this macro so that it would cycle through the 50
different types of errors that I have??

Some of the errors are simple typos -- change MIrror to Mirror or
change iwojima to Iwo Jima
Some need to be put into a consistent format to be imported into
another application; e.g, -- change 1" to 1 in. or change 9" to 9 in.

Thanks.

Barb

 

[Dave Peterson]

This code could be modified, but the first thing I'd try is to do a bunch of
edit|Replaces. (If you don't ever see the "formula too long" warning, then this
would be over kill.)

Since you're doing this a lot, you could create a dedicated workbook that has a
list of words to fix and right next to each word, put the correction.

Then open this workbook and your workbook that should be fixed and run this
macro:

Option Explicit
Sub testme()

Dim myWordsToFix As Range
Dim myCell As Range

With ThisWorkbook.Worksheets("myTableSheetNameGoesHere")
'with headers in A1 and B1
Set myWordsToFix = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myWordsToFix.Cells
ActiveSheet.Cells.Replace What:=myCell.Value, _
Replacement:=myCell.Offset(0, 1).Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next myCell

End Sub