CODEWITHSUNDEEP
vbaexcel
cookiemonster
cookiemonster

Reputation: 368

VBA not looping filtered rows

I have this big excel file that is automated to do some processes. Today, I figured there is an issue with one of the columns and I need to fix it. to fix it I am generated this code below to filter column N to show all '#N/A'. with the filtered rows, I want to check and see if the offset to the right 2 columns has the value "Available". if it does, I want to loop through all column N and replace the '#N/A' with 'Unkown'. but the code I generated only works for the first filtered cell and doesn't loop.

Sub tess()
ActiveSheet.Range("$C$1:$AR$468").AutoFilter Field:=12, Criteria1:="#N/A"

ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(, 12).Select

Dim lr
lr = ActiveSheet.UsedRange.Rows.CountLarge

For Each cell In Range("n1:n" & lr)
If ActiveCell.Value = CVErr(xlErrNA) And ActiveCell.Offset(, 2).Value = "Available" Then

  ActiveCell.Value = "Unkown Person"

  End If
Next cell

End Sub

Thank you.

Upvotes: 0

Views: 58

Answers (2)

DisplayName
DisplayName

Reputation: 13386

you can avoid looping by adding another filter:

Sub tess()
    With ActiveSheet.Range("$C$1:$AR$468")
        .AutoFilter Field:=12, Criteria1:="#N/A"
        .AutoFilter Field:=14, Criteria1:="Available"
        If Application.WorksheetFunction.Subtotal(103, .Columns(12)) > 1 Then .Offset(1, 11).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible).Value = "Unkown Person"
        .Parent.AutoFilterMode = False
    End With
End Sub

Upvotes: 2

Excel Developers
Excel Developers

Reputation: 2825

You should refer to "cell" within your For loop, not "ActiveCell".

Upvotes: 1

Related Questions