formula is to long error
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
--
Regards,
Tom Ogilvy
"Tom Peacock" <tpeacockathoustondotrrdotcom wrote in message
...
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?
--
Tom Peacock
|