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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Not knowing what you are searching for, not what you are replacing this with,
makes it impossible to help you!
 
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!
 
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
 
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
 
Back
Top