VBA Autofilter- Once filter is run, can I insert text alongside any remaining results?-CodePudding

I'm a total beginner, so if my code is terrible I apologize. I'm trying to write a macro that will insert a text phrase alongside any data that is displayed after my autofilter is run on Sheet3. I am ignoring any differences less than or equal to $2, but if there are differences greater than that amount I want to display the following text in column L on whatever row my first result is in. Text is "Please review 'Differences' & advise of any changes in participant status, contribution amounts, etc."

If there are no results, no descriptor text is needed.

Column A is the person's name, and column K is the "difference." Column L is where I wanted my notes to go. My assumption was that after running the autofilter, I could find out if there was any text in Column A (after the header in A3) and if so, display my pre-made notes in the notes section under Column L.

Current code is as follows:

Worksheets("Sheet3").Select
Range("A2:L300").AutoFilter Field:=12, VisibleDropDown:=False, Criteria1:=">2", Operator:=xlOr, Criteria2:="<-2"
Dim ws As Worksheet
Set ws = Worksheets("Sheet3")
If Application.WorksheetFunction.IsText(ws.Range("A3:A300")) = True Then
ws.Range("L3") = "Please review 'Differences' & advise of any changes in participant status, contribution amounts, etc."
End If

I am very slowly building up an understanding of VBA programming in general, so this is my best effort based on copying other people's code that is close to what I am attempting. If anyone can help me out, it would be greatly appreciated!

CodePudding user response：

After running a filter you can use SpecialCells(xlCellTypeVisible) to get any visible rows, and loop over them:

Sub Tester()
    Dim ws As Worksheet, rngVis As Range, c As Range
    
    Set ws = Worksheets("Sheet3")
    
    With ws.Range("A2:L300")
        .AutoFilter Field:=12, VisibleDropDown:=False, _
             Criteria1:=">2", Operator:=xlOr, Criteria2:="<-2"
        On Error Resume Next  'ignore error if no visible rows
        Set rngVis = .Columns(1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0       'stop ignoring errors
    End With
    
    If Not rngVis Is Nothing Then    'any visible rows?
        For Each c In rngVis.Cells   'loop over visible cells in ColA
            If Len(c.Value) > 0 Then 'cell has a value?
                c.EntireRow.Columns("L").Value = _
                  "Please review 'Differences' & advise of any changes " & _
                  "in participant status, contribution amounts, etc."
            End If
        Next c
    End If

End Sub