PDA

View Full Version : Solved: error when looping


satyen
03-30-2010, 04:25 AM
Wasn’t sure whether to start a new thread or not. But because the other one is now resolved, I thought I would.

This is erroring out on


oSh.Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Select



Sub Empty_Sheets()

Dim oSh As Worksheet


For Each oSh In ActiveWorkbook.Worksheets


Select Case oSh.Name
Case "a", "b", "c"

oSh.Cells.EntireRow.Hidden = False
oSh.Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Select

oSh.Selection.EntireColumn.Select
oSh.Range(Selection, Selection.End(xlToLeft)).Select
oSh.Selection.ClearContents
oSh.Range("A1").Select

Debug.Print oSh.Name

End Select

Next oSh

End Sub
Bob Phillips
03-30-2010, 04:40 AM
Presumably, it is erroring because there is nothing found.

You should not select, but set a range object to the find range, and test it for nothing, which means not found.
satyen
03-30-2010, 05:38 AM
Total is there on all sheets. When I select the sheet it works, just not automatically
Bob Phillips
03-30-2010, 05:39 AM
POst a workbbok to try it on please.
Paul_Hossler
03-30-2010, 05:50 AM
It would seem to me that the .Find would be dependent on where the ActiveCell is on any given sheet.

What happens if you just delete the After:=ActiveCell ?

I'd still follow XLD's suggetion to test for a not found condition also



oSh.Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Select


Paul
satyen
03-30-2010, 06:16 AM
I did this instead and it worked.


Sub macrotest ()
Sheets(Array("a", "b", "c")).Select
Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select


Selection.EntireColumn.Select
Range(Selection, Selection.End(xlToLeft)).Select
' ActiveWorkbook.Names.Add Name:="TRange", RefersToR1C1:=Selection
Selection.Clear
'Cells.Selection.ClearContents
Range("A1").Select

End Sub