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
