loop then delete unwanted cell then loop through worksheets

[liam1289]

i have one problem you might be able to help me with.
loop through 1 worksheet then delete what criteria i dont want then move onto the next worksheet in my workbook and do the same...
here is some of my code


Private Sub CommandButton3_Click()
'class
'inputbox find class
'generate new Workbook with the 5days init and delete wotever class wasnt picked
'loop through the sheet 9 hours and 13 class rooms
Dim g, block, start As Range
Dim ws As Worksheet
Dim class As String
Const maxrooms = 13
Const maxhours = 9
class = InputBox("Enter Class")

'if it is not the class selected then delete+ othwrise copy to new template

Set start = Range("B3")
Set block = Range(start, start.Offset(maxhours - 1, maxrooms - 1))
For Each g In block
g.Activate
Next g
If Value = class Then Delete (ActiveCell)




End Sub

Sub test()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
MsgBox ws.Name
Next ws
End Sub

 

[VoG]

Like this?

Private Sub CommandButton3_Click()
Dim g As Range, block As Range, start As Range
Dim ws As Worksheet
Dim class As String
Const maxrooms = 13
Const maxhours = 9
class = InputBox("Enter Class")
For Each ws In ActiveWorkbook.Worksheets
    With ws
        Set start = .Range("B3")
        Set block = .Range(start, start.Offset(maxhours - 1, maxrooms - 1))
        For Each g In block
            If g.Value = class Then g.ClearContents
        Next g
    End With
Next ws
End Sub

 

[liam1289]

ya thats the type of code im looking for but for some reason it does seem to do the loop. then range doesnt work

 

[VoG]

It worked when I tested it. I put in a message box to show the range

Private Sub CommandButton3_Click()
Dim g As Range, block As Range, start As Range
Dim ws As Worksheet
Dim class As String
Const maxrooms = 13
Const maxhours = 9
class = InputBox("Enter Class")
For Each ws In ActiveWorkbook.Worksheets
    With ws
        Set start = .Range("B3")
        Set block = .Range(start, start.Offset(maxhours - 1, maxrooms - 1))
        MsgBox ws.Name & "!" & block.Address(False, False)
        For Each g In block
            If g.Value = class Then g.ClearContents
        Next g
    End With
Next ws
End Sub

 

[liam1289]

ya that works fine on the first sheet thanks.but when i try to move onto the 2nd and 3rd sheet etc it comes up with a error msg of the clear contents code and doesnt work on the other sheets. they other sheets are the exact same range as the 1st..

Ps thanks for your help

 

[VoG]

Are some sheets protected? I just tested it with 4 sheets and it ran without error.

 

[liam1289]

ok i will check im using an old version of excel so i will try it later on excel 2007/thanks for your help mate

 

[liam1289]

im afraid it works fine for the first sheet but then i get this error msg
"clearcontents method of range class failed"

can you help?

 

[VoG]

Sorry but I cannot reproduce that error.

Try with a new workbook - does it still error?