Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula is to long error
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula is to long error
Thanks Tom.
I finally found some time to apply your code. It was a big help. -- Tom Peacock "Tom Ogilvy" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula too long error | Excel Discussion (Misc queries) | |||
Formula Too Long error | Excel Worksheet Functions | |||
formula is too long error | Excel Worksheet Functions | |||
Formula too long error... | Excel Discussion (Misc queries) | |||
formula too long error | Excel Discussion (Misc queries) |