Looping over a worksheet

[stephen]

Hello. I'd like to loop over all rows in a worksheet, and hide those
matching certain criteria.

The criteria are:
1. Cell in column 'H' equals "PASS"
2. Cell in column 'M' contains the strings "POUT" and "DIFF"

I have so far:

Sub show_diffs()
'
' Hide all rows except those showing POUT and DIFF
' except for rows with FAIL
'
Application.Goto Range("A2")
ActiveCell.EntireRow.Select
Do While ActiveCell.Value <> ""
if selection ????????
Selection.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).EntireRow.Select
Loop
End Sub

Thanks.

Stephen

 

[Bob Phillips]

Sub show_diffs()
Dim i As Long
Application.Goto Range("A2")
With ActiveCell
Do While .Offset(i,0).Value <> ""
If .Offset(i,7).Value = "PASS" AND _
(.Offset(i,12).Value = "POUT" OR .Offset(i,12).Value =
"DIFF") Then
.Offset(i,0).EntireRow.Hidden = False
End If
i = i + 1
Loop
End With
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[PCLIVE]

One way:

Sub Macro2()
For Each cell In Range("M1:M100")
If cell.Value = "POUT" _
Then
GoTo Bypass
Else
If cell.Value = "DIFF" _
Then
GoTo Bypass
Else
cell.EntireRow.Hidden = True
End If
End If
Bypass:
Next cell

End Sub


Regards,
Paul

 

[stephen]

Thanks to you both for the ideas. That takes care of the looping part.

Are there any string functions that tell whether a substring is in a
string? Is this valid:

If "Joe" in "Joe was here" then
....
Endif
?

Stephen

 

[stephen]

Thanks to everyone's help, I've gotten this far.

I'm receiving a "Loop without Do" error that I don't quite understand:

Sub show_diffs()
Dim Substring1, Substring2, FindinString, MyPos
Dim i As Long
Substring1 = "POUT"
Substring2 = "DIFF"

i = 1
Application.Goto Range("A2")
With ActiveCell
Do While .Offset(i, 0).Value <> ""
.Offset(i, 0).EntireRow.Hidden = True
FindinString = .Offset(i, 12).Value
If InStr(FindinString, Substring1) Then
If InStr(FindinString, Substring2) Then
.Offset(i, 0).EntireRow.Hidden = False
End If
End If
If .Offset(i, 8) = "FAIL" Then
.Offset(i, 0).EntireRow.Hidden = False
i = i + 1
Loop
End With
End Sub

 

[PCLIVE]

Looks like you have a block if statement that is not ended.

If .Offset(i, 8) = "FAIL" Then
.Offset(i, 0).EntireRow.Hidden = False
i = i + 1

I'm not sure what you wanted to do here. You can either add an underscore "
_" next to Then, or you'll need to place an "End If" somewhere. Either
above or below the "i= i + 1".

HTH,
Paul

 

[Bob Phillips]

If "Joe in here " like "*Joe*

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Thanks to you both for the ideas. That takes care of the looping part.

Are there any string functions that tell whether a substring is in a
string? Is this valid:

If "Joe" in "Joe was here" then
....
Endif
?

Stephen

 

[Bob Phillips]

These two lines


If .Offset(i, 8) = "FAIL" Then
.Offset(i, 0).EntireRow.Hidden = False

should be three


If .Offset(i, 8) = "FAIL" Then
.Offset(i, 0).EntireRow.Hidden = False
End If


and you should not set i = 1 before the loop, let it default to 0.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[stephen]

These two lines


If .Offset(i, 8) = "FAIL" Then
.Offset(i, 0).EntireRow.Hidden = False

should be three


If .Offset(i, 8) = "FAIL" Then
.Offset(i, 0).EntireRow.Hidden = False
End If

Thanks! That did the trick. Lots to learn ...

Stephen