[SOLVED] Select next cell in filtered large amount of data

Select next cell in filtered large amount of data - VBA

I've been working on a code that will filter column CF for all "No" and then look to the cell to the right of the "No" to see if the number listed is "2". If the number is "2" then it will replace the contents of the cell in column D with "1 2 3".

So far the code is working for that purpose but I am lost on how to get it to go to the next "No" function in column CF and repeat the code for all "No"s. I am working with 50,000+ rows of data so every time I try to run an .offset function to find the next visible cell it crashes.

Any ideas on a loop to run the function so it jumps to the next "No" valuable visible in the autofilter?

Much appreciated.

Sub CorrectMealSwap()

ActiveSheet.Range("$CF$1").AutoFilter Field:=1, Criteria1:="No"
    With Worksheets("Worksheet_1").AutoFilter.Range
        Range("CF" & .Offset(1, 0).SpecialCells(xlCellTypeVisible_ 
        (1).Row).Select
    End With
    If ActiveCell.Offset(0, 1) = "2" Then
    ActiveCell.Offset(0, -80).Select
    ActiveCell.Value = "1 2 3"
    End If

 End Sub

Solution

you can avoid looping through filtered cells by applying another filter on column CG

Sub CorrectMealSwap()
    With ActiveSheet 
        With .Range("CG1", .Cells(.Rows.Count, "CF").End(xlUp)) 'reference referenced sheet columns CF:CG cells from row 1 (header) down to last not empty row of column CF
            .AutoFilter Field:=1, Criteria1:="No" 'filter on referenced range 1st column with "No"
            .AutoFilter Field:=2, Criteria1:="2" 'filter on referenced range 2nd column with "2"
            If Application.WorksheetFunction.Subtotal(103, .Columns(1)) > 1 Then .Resize(.Rows.Count - 1, 1).Offset(1, -80).SpecialCells(xlCellTypeVisible).Value = "1 2 3" ' if any filtered cell other than header then write "a 2 3" in corresponding column D cells 
        End With
        .AutoFilterMode = False
    End With
 End Sub