cells.find

[Irek1974]

Hi,
I have a file and a couple of sheets in it (names of the sheets for example : 16 , 17 , 19). In all the sheets there are accounts numbers in rows from 1 to 100.
I need to "visit" sheet after sheet and find accont number 551-300 and delete that row.
if nothing is found in one sheet - the macro should go to the next sheet. it should work till it visits all the sheets. the last sheets name is "19".

Sub delete_account()
Sheets(Array(1)).Select
Do Until ActiveSheet.Name = "19"
On Error GoTo missingACCOUNT
Cells.Find(what:="551-300").Select
missingACCOUNT:
ActiveSheet.Next.Select
Loop
End Sub

I have an error message when it goes to the secon sheet (it stops on line : cells.find ....).
Then I wrote simplest code :

sub delete_account2 ()
Sheets("16").Select
On Error GoTo missing116
Cells.Find(what:="551-300").EntireRow.Select
missing16:
Sheets("17").Select

On Error GoTo missing17
Cells.Find(what:="551-300").EntireRow.Select
missing17:
Sheets("19").Select

On Error GoTo missing19
Cells.Find(what:="551-300").EntireRow.Select
missing19:
exit sub

End Sub

but also this time I get an error message when it goes to the second sheet ("17"). The error message appears when macro goes to line :
cells.find ....

could anyone help pls ?
I.

 

[Gary McMaster]

Assuming there is only one instance of the account number on each sheet, something like this should work.

Gary

Public Sub Test()

Dim oSheet As Worksheet
Dim oFound As Range

For Each oSheet In ThisWorkbook.Worksheets
    Set oFound = oSheet.UsedRange.Find(what:="551-300")
    If Not oFound Is Nothing Then oFound.EntireRow.Delete
Next oSheet

End Sub

 

[Irek1974]

it works, thank you but I need one modification.
in this excel file I have also sheets "TB" and "COS" which should be excluded from deleting account 551-300
could you please modify the code ?

Assuming there is only one instance of the account number on each sheet, something like this should work.

Gary

Public Sub Test()
 
Dim oSheet As Worksheet
Dim oFound As Range
 
For Each oSheet In ThisWorkbook.Worksheets
    Set oFound = oSheet.UsedRange.Find(what:="551-300")
    If Not oFound Is Nothing Then oFound.EntireRow.Delete
Next oSheet
 
End Sub

 

[Gary McMaster]

Public Sub Test()

Dim oSheet As Worksheet
Dim oFound As Range

For Each oSheet In ThisWorkbook.Worksheets
    If oSheet.Name <> "TB" And oSheet.Name <> "COS" Then
        Set oFound = oSheet.UsedRange.Find(what:="551-300")
        If Not oFound Is Nothing Then oFound.EntireRow.Delete
    End If
Next oSheet

End Sub

 

[Irek1974]

ooooooo - that was so simple. I did'nt think about such a solution
thank you