Solved: error when looping

[satyen]

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

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

[VBA]
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
[/VBA]

[Bob Phillips]

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]

Total is there on all sheets. When I select the sheet it works, just not automatically

[Bob Phillips]

POst a workbbok to try it on please.

[Paul_Hossler]

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


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

Paul

[satyen]

I did this instead and it worked.

[vba]
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
[/vba]