referencing visible cells in filtered row in a formula

[wbstadeli]

Hi All,

I have a macro that inputs a formula in 5 rows in named range in Sheet1. The formula references "static" cell ranges in Sheet2 that are inside of a table. This all works fine, except now I want to filter my table in Sheet2 and have the formula reference the visible row instead of the "static" range. How can i do this?

Info:
Sheet 1 is named "Quote Sheet"
-has named range ""EntriesFromStatistics"
Sheet 2 is named "Imported Jobs - Reference Sheet"
-has only one table in it, but the table name can vary


This is current code:

Sub EntriesFromStatistics()
Worksheets("Quote Sheet").Activate
Dim R As Range, rws As Long
Set R = Range("EntriesFromStatistics")
rws = R.Rows.Count
With R
.Rows(.Rows.Count - 4).Select
Selection.Formula = "=IFERROR((SUM('Imported Jobs - Reference Sheet'!J2:O2)*60)/'Imported Jobs - Reference Sheet'!G2,""Invalid Entry"")"
Selection.Value = Selection.Value
.Rows(.Rows.Count - 3).Select
Selection.Formula = "=IFERROR((SUM('Imported Jobs - Reference Sheet'!J3:O3)*60)/'Imported Jobs - Reference Sheet'!G3,""Invalid Entry"")"
Selection.Value = Selection.Value
.Rows(.Rows.Count - 2).Select
Selection.Formula = "=IFERROR((SUM('Imported Jobs - Reference Sheet'!J4:O4)*60)/'Imported Jobs - Reference Sheet'!G4,""Invalid Entry"")"
Selection.Value = Selection.Value
.Rows(.Rows.Count - 1).Select
Selection.Formula = "=IFERROR((SUM('Imported Jobs - Reference Sheet'!J5:O5)*60)/'Imported Jobs - Reference Sheet'!G5,""Invalid Entry"")"
Selection.Value = Selection.Value
.Rows(.Rows.Count).Select
Selection.Formula = "=IFERROR((SUM('Imported Jobs - Reference Sheet'!J6:O6)*60)/'Imported Jobs - Reference Sheet'!G6,""Invalid Entry"")"
Selection.Value = Selection.Value
End With
End sub

I need the references in red to be something like :
Selection.Formula = "=IFERROR((SUM('First visible row in filtered table in sheet "Imported Jobs - Reference Sheet" columns J thru O)*60)/First visible row in filtered table in sheet "Imported Jobs - Reference Sheet" column G,""Invalid Entry"")"
Selection.Value = Selection.Value
.Rows(.Rows.Count - 3).Select
Selection.Formula = "=IFERROR((SUM('Second visible row in filtered table in sheet "Imported Jobs - Reference Sheet" columns J thru O)*60)/Second visible row in filtered table in sheet "Imported Jobs - Reference Sheet" column G,""Invalid Entry"")"
Selection.Value = Selection.Value
etc...

Thanks for any help!

 

[DanteAmor]

Try with this

Sub EntriesFromStatistics()
    Dim R As Range, h2 As Worksheet, n As Long
    '
    Worksheets("Quote Sheet").Activate
    Set R = Range("EntriesFromStatistics")
    Set h2 = Sheets("Imported Jobs - Reference Sheet")
    n = 4
    For i = 2 To h2.Range("J" & Rows.Count).End(xlUp).Row
        If h2.Rows(i).Hidden = False Then
            With R.Rows(R.Rows.Count - n)
                .Formula = "=IFERROR((SUM('" & h2.Name & "'!J" & i & ":O" & i & ")*60)/'" & h2.Name & "'!G" & i & ",""Invalid Entry"")"
                .Value = .Value
            End With
            n = n - 1
            If n < 0 Then Exit For
        End If
    Next
End Sub

 

[wbstadeli]

Try with this

Sub EntriesFromStatistics()
    Dim R As Range, h2 As Worksheet, n As Long
    '
    Worksheets("Quote Sheet").Activate
    Set R = Range("EntriesFromStatistics")
    Set h2 = Sheets("Imported Jobs - Reference Sheet")
    n = 4
    For i = 2 To h2.Range("J" & Rows.Count).End(xlUp).Row
        If h2.Rows(i).Hidden = False Then
            With R.Rows(R.Rows.Count - n)
                .Formula = "=IFERROR((SUM('" & h2.Name & "'!J" & i & ":O" & i & ")*60)/'" & h2.Name & "'!G" & i & ",""Invalid Entry"")"
                .Value = .Value
            End With
            n = n - 1
            If n < 0 Then Exit For
        End If
    Next
End Sub

I apologize for taking so long to get back to you, but this is brilliant code sir!! It works wonderful, thank you very much!!

 

[DanteAmor]

I apologize for taking so long to get back to you, but this is brilliant code sir!! It works wonderful, thank you very much!!

I'm glad to help you. Thanks for the feedback.