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.

 

[pitchoute]

Could you try it this way?

Sub InsertFormulaInVisibleCells()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    Set ws = ActiveSheet

    If ws.Range("S1").EntireColumn.Hidden Then
        ws.Columns("S").Hidden = False
    ElseIf Application.WorksheetFunction.CountA(ws.Columns("S")) = 0 Then
        ws.Columns("S").Insert Shift:=xlToRight
    End If

    lastRow = ws.Cells(ws.Rows.Count, "Q").End(xlUp).Row

    For i = 2 To lastRow
        If Not ws.Rows(i).Hidden Then
            ws.Cells(i, "S").FormulaR1C1 = "=INT(RC[-2])"
        End If
    Next i
End Sub

 

[Skyybot]

Sub AddFormulaToVisibleRows()
Dim wb As Workbook, sht As Worksheet
Dim cell As Range, rng As Range

Set wb = Workbooks("Book1.xlsx")
Set sht = wb.Sheets("Sheet1")
Set rng = sht.Range("S2", Cells(sht.UsedRange.Rows.Count, "S"))

For Each cell In rng
    If sht.Rows(cell.Row).Hidden = False Then
        cell.FormulaR1C1 = "=INT(RC[-2])"
    End If
Next cell

End Sub

 

[n0tgirl]

Sub AddFormulaToVisibleRows()
Dim wb As Workbook, sht As Worksheet
Dim cell As Range, rng As Range

Set wb = Workbooks("Book1.xlsx")
Set sht = wb.Sheets("Sheet1")
Set rng = sht.Range("S2", Cells(sht.UsedRange.Rows.Count, "S"))

For Each cell In rng
    If sht.Rows(cell.Row).Hidden = False Then
        cell.FormulaR1C1 = "=INT(RC[-2])"
    End If
Next cell

End Sub

 

[n0tgirl]

This worked. I had to tweak the wb and sht to match my info, but I was able to loop through and get the formula to populate in all visible cells in column S.

    Set wb = ActiveWorkbook
    Set sht = ActiveSheet
    Set rng = sht.Range("S2", Cells(sht.UsedRange.Rows.Count, "S"))

    For Each cell In rng
    If sht.Rows(cell.Row).Hidden = False Then
        cell.FormulaR1C1 = "=INT(RC[-2])"
    End If
    Next cell

This worked! Thank you so much

 

[n0tgirl]

Could you try it this way?

Sub InsertFormulaInVisibleCells()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    Set ws = ActiveSheet

    If ws.Range("S1").EntireColumn.Hidden Then
        ws.Columns("S").Hidden = False
    ElseIf Application.WorksheetFunction.CountA(ws.Columns("S")) = 0 Then
        ws.Columns("S").Insert Shift:=xlToRight
    End If

    lastRow = ws.Cells(ws.Rows.Count, "Q").End(xlUp).Row

    For i = 2 To lastRow
        If Not ws.Rows(i).Hidden Then
            ws.Cells(i, "S").FormulaR1C1 = "=INT(RC[-2])"
        End If
    Next i
End Sub

I tried this but it was unsuccessful. It never put the formula in column S and just went to my next line of code. It looks like Skyybot came up with a solution that seems to work for me. I appreciate you trying to help me out!!!

 

[MARK858]

fill down to the last row in column S

@n0tgirl how can it fill down to the last row in column S if you have just inserted the column, it would be blank?

Then I am inserting a new column S

Are you sure you don't mean to the last row in column N or Q?

If yes then change the red S in the code pitchoute posted in post 2 to the correct column

Set rngVisible = ws.Range("S2:S" & ws.Cells(ws.Rows.Count, "S").End(xlUp).Row).SpecialCells(xlCellTypeVisible)