VBA - Insert Formula on First Visible Row; Active ROW in Formula

[NEW_2VBA]

[TABLE="class: outer_border, width: 758, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]DATA
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Name
[/TD]
[TD]ReportID
[/TD]
[TD]Date
[/TD]
[TD]Conf. #
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]KDWZ
[/TD]
[TD]A44
[/TD]
[TD]1/1/19

[/TD]
[TD]=INDEX(Sheet2!C:C,MATCH(B3,Sheet2!B:B)

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]BLSN
[/TD]
[TD]B62
[/TD]
[TD]1/5/19
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]FIVT
[/TD]
[TD]Z73
[/TD]
[TD]4/5/18
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hi! Is there a way to insert a formula on he first visible row after headers on a filtered list?
Can I reference a specific cell in the active row within the formula? Example: B3 in formula above would be relative to active row; it should always look for column B


I tried something like this in code --> Range("D" & ActiveCell.Row.Formula = "=INDEX(Sheet2!C:C,MATCH(B3,Sheet2!B:B)

 

[Norie]

You could use R1C1 notation.

Range("D:D").SpecialCells(xlCellTypeVisible).Cells(1,1).FormulaR1C1 = "=INDEX(Sheet2!C3, MATCH(RC[-2], Sheet2!C2))"