MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Loop problem


Posted by DAVID on February 12, 2002 10:46 AM

How can i loop a worksheet but only the filtered list.
This is the code im using , but this goes thru the whole sheet .
I need something that goes thru the filtered list only
Sub DISCC()
Range("A2").Activate
Do
If ActiveCell.Offset(0, 4).Value = "X" And ActiveCell.Offset(0, 2).Value > 0 Then

ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(0, 1) - ActiveCell.Offset(0, 1) * ActiveCell.Offset(0, 2)

End If
ActiveCell.Offset(1, 0).Activate
Loop Until ActiveCell = Empty
Range("A1").Activate

End Sub


Posted by Tybalt on February 12, 2002 1:29 PM


Sub DISCC()
Dim rng As Range, cell As Range
Set rng = Range(Range("A2"), Range("A2").End(xlDown))
For Each cell In rng
With cell
If Not .EntireRow.Hidden Then
If .Offset(0, 4).Value = "X" And .Offset(0, 2).Value > 0 Then
.Offset(0, 3).Value = .Offset(0, 1) - .Offset(0, 1) * .Offset(0, 2)
End If
End If
End With
Next
Range("A1").Activate
End Sub

Posted by Tybalt on February 12, 2002 1:36 PM

Or ....

..... this is better (more efficient) :-

Sub DISCC()
Dim rng As Range, cell As Range
Set rng = Range("A1").CurrentRegion
Set rng = rng.SpecialCells(xlVisible)
For Each cell In rng
With cell
If .Offset(0, 4).Value = "X" And .Offset(0, 2).Value > 0 Then
.Offset(0, 3).Value = .Offset(0, 1) - .Offset(0, 1) * .Offset(0, 2)
End If
End With
Next
Range("A1").Activate
End Sub


Posted by DAVID on February 13, 2002 8:28 AM

Re: Or ....

The code works fine when i dont put the "AND" in the if statement, however if i put the "AND" clause then i get an error TYPE MISMATCH message any help would be appreciated

Posted by Tybalt on February 13, 2002 4:44 PM

Try this ....


Sub DISCC()
Dim rng As Range, cell As Range
Set rng = Range("A1").CurrentRegion
Set rng = Intersect(rng.SpecialCells(xlVisible), Columns(1))
For Each cell In rng
With cell
If .Offset(0, 4).Value = "X" And IsNumeric(.Offset(0, 2)) And .Offset(0, 2).Value > 0 Then
.Offset(0, 3).Value = .Offset(0, 1) - .Offset(0, 1) * .Offset(0, 2)
End If
End With
Next
Range("A1").Activate
End Sub