formula is to long error

  • Thread starter Thread starter Tom Peacock
  • Start date Start date
T

Tom Peacock

Using Excel 2000 on Windows XP Home.



After performing a web query, I need to do a search and replace on the new
data eliminating a minus sign "-". The sign turn up because the web page
uses it as a marker for an down arrow graphic that represents a downward
revision in the numbers I am interested in. Excel interprets it as a
negative sign in front of the number and I have been unable to persuade
Excel to ignore the sign. The plus sign for the up arrow, of course does
not create the same difficulty.



I thought of using a search and replace but the query returns some cells
that have too much text in them and the replace fails and halts giving an
excuse that the "formula is to long." The text in these cells is not
important to me, they always start a row 8 but continue for a variable
number of rows. Immediately following that block is a cell that says "EPS
(USD, Major) ".



I tried the following macro, but when it encounters the error message it
just moves on to the next sheet, leaving many sheets improperly edited.



Sub RemoveMinusSign()

On Error Resume Next

cnt = ActiveWorkbook.Worksheets.Count

For n = 1 To cnt

Cells.Replace What:="-", Replacement:="", LookAt:=xlPart, SearchOrder:=
_

xlByRows, MatchCase:=False

ActiveSheet.Next.Select

Next n

End Sub



My question is what is the best way to stop getting this error?



My thoughts are that I could use an improved macro to continue removing the
minus sign after encountering the error or the one I favor, eliminating the
offending rows. However this is getting beyond my VBA skills. Any
suggestions?
 
Dim sh as Worksheet
Dim rng as range, rng1 as range
Dim rng2 as range

for each sh in Worksheets
sh.Activate
set rng = Activesheet.Usedrange
set rng1 = nothing
set rng1 = Cells.Find(What:="EPS (USD, Major) ", _
LookAt:=xlPart)
if rng1 is nothing then _
msgbox "Can't find start of data: " & sh.name
Else


set rng2 = Intersect(Range(rng1,cells(rows.count, _
columns.count)),rng)
rng2.Replace What:="-", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End if
Next


or - delete the rows

Dim sh as Worksheet
Dim rng as range, rng1 as range
Dim rng2 as range

for each sh in Worksheets
sh.Activate
set rng = Activesheet.Usedrange
set rng1 = nothing
set rng1 = Cells.Find(What:="EPS (USD, Major) ", _
LookAt:=xlPart)
if rng1 is nothing then _
msgbox "Can't find start of data: " & sh.name
Else

Range(rows(8), rng1.EntireRow).Entirerow.Delete

cells.Replace What:="-", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End if
Next
 
Thanks Tom.

I finally found some time to apply your code. It was a big help.


--
Tom Peacock


Tom Ogilvy said:
Dim sh as Worksheet
Dim rng as range, rng1 as range
Dim rng2 as range

for each sh in Worksheets
sh.Activate
set rng = Activesheet.Usedrange
set rng1 = nothing
set rng1 = Cells.Find(What:="EPS (USD, Major) ", _
LookAt:=xlPart)
if rng1 is nothing then _
msgbox "Can't find start of data: " & sh.name
Else


set rng2 = Intersect(Range(rng1,cells(rows.count, _
columns.count)),rng)
rng2.Replace What:="-", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End if
Next


or - delete the rows

Dim sh as Worksheet
Dim rng as range, rng1 as range
Dim rng2 as range

for each sh in Worksheets
sh.Activate
set rng = Activesheet.Usedrange
set rng1 = nothing
set rng1 = Cells.Find(What:="EPS (USD, Major) ", _
LookAt:=xlPart)
if rng1 is nothing then _
msgbox "Can't find start of data: " & sh.name
Else

Range(rows(8), rng1.EntireRow).Entirerow.Delete

cells.Replace What:="-", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End if
Next
Click to expand...
 
Back
Top