Find next visible cell, add a formula and then fill column down

[n0tgirl]

Hello,
I have tried searching through old posts but nothing seems to fit my scenario, and I am such a VBA novice that I don't know how to pick out the parts that could potentially work. I have a macro that is filtering column N for the word "done." Then I am inserting a new column S and want to add a formula (to find the clean date from column Q) in all visible cells in S.

Range("S2").Select
ActiveCell.FormulaR1C1 = "=INT(RC[-2])"

I realized that it is not working properly because the range that I have selected is not looking at the visible cell in column S, just starting in cell S2 and then the rest of my macro is providing bad data. What code would I use to find that first visible cell in Column S, regardless of it starting in Cell S6 or cell S266?

The next problem that I am having is that I would like to copy that formula to get my clean date in that visible cell and fill down to the last row in column S, so that is providing the date that is in the corresponding row in column Q. I have declared lastRow as Long

lastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Any insight will be appreciated.

 

[pitchoute]

Could you please try and share the result?

Sub InsertFormulaInVisibleCells()
    Dim ws As Worksheet
    Dim rngVisible As Range

    Set ws = ActiveSheet

    On Error Resume Next
    Set rngVisible = ws.Range("S2:S" & ws.Cells(ws.Rows.Count, "S").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If Not rngVisible Is Nothing Then
        rngVisible.FormulaR1C1 = "=INT(RC[-2])"
    Else
        MsgBox "No visible cells found in Column S after filtering."
    End If
End Sub

 

[n0tgirl]

Could you please try and share the result?

Sub InsertFormulaInVisibleCells()
    Dim ws As Worksheet
    Dim rngVisible As Range

    Set ws = ActiveSheet

    On Error Resume Next
    Set rngVisible = ws.Range("S2:S" & ws.Cells(ws.Rows.Count, "S").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If Not rngVisible Is Nothing Then
        rngVisible.FormulaR1C1 = "=INT(RC[-2])"
    Else
        MsgBox "No visible cells found in Column S after filtering."
    End If
End Sub

I pasted in the code above and when I step through, it just continues on to my next line of code after the end if. I am not getting any messages and S6 (my next visible cell) does not have the formula in it. It is just blank.