VBA Named Range Dynamic Index-CountIf Formula

[Hamartian_]

Hello,
I'm looking for some help with the last piece of this code, as everything else works until I attempt to change Data_Validation[Layer] into a variable equivalent to the DataBodyRange column of the ActiveCell

This is the only area that breaks the code for me, where I attempt to assign a variable with
":INDEX(Data_Validation[Layer],COUNTIF(Data_Validation[Layer],""?*""))"

to

":INDEX(" & ActiveCell.ListObject.DataBodyRange.Column & ",COUNTIF(" & ActiveCell.ListObject.DataBodyRange.Column & ",""?*""))"

Looking forward to any guidance! Learned a lot to get this far, and I know there's a lot to go

Rest of the code:

Sub Create_DV_NamedRange()
'creates a named range based on header name that dynamically ignores blank cells

Dim TableName As String
Dim HeaderName As String
Dim HeaderOffset As Range
Dim startingCell As String
Dim DV_Name As String
Dim DataName As String

TableName = ActiveCell.ListObject.Name
HeaderName = Intersect(ActiveCell.ListObject.HeaderRowRange, ActiveCell.EntireColumn).Value
DV_Name = "DV_" & HeaderName
'DataName = "Data_Validation[" & HeaderName & "]"
startingCell = Intersect(HeaderOffset, ActiveCell.EntireColumn).Address

Set HeaderOffset = Intersect(ActiveCell.ListObject.HeaderRowRange, ActiveCell.EntireColumn).Offset(1)

'  Can not get following to work with references of strings, variants, or ranges :INDEX(Data_Validation[Layer],COUNTIF(Data_Validation[Layer],""?*""))"

'  Works - Index and CountIf is not dynamic - Recorded from Macro and modified
'  ActiveWorkbook.Names.Add Name:=DV_Name, RefersToR1C1:= _
'      "='Data Validation'!R3C" & ActiveCell.Column & ":INDEX(Data_Validation[Layer],COUNTIF(Data_Validation[Layer],""?*""))"
'  ActiveWorkbook.Names(DV_Name).Comment = ""

 'Does Not Work
ActiveWorkbook.Names.Add Name:=DV_Name, RefersToR1C1:= _
       "='Data Validation'!R3C" & ActiveCell.Column & ":INDEX(" & ActiveCell.ListObject.DataBodyRange.Column & ",COUNTIF(" & ActiveCell.ListObject.DataBodyRange.Column & ",""?*""))"
ActiveWorkbook.Names(DV_Name).Comment = ""


'  Does Not Work
'  ActiveWorkbook.Names.Add Name:=DV_Name, RefersToR1C1:= _
'      "='Data Validation'!R" & HeaderOffset.Row & "C" & ActiveCell.Column & ":INDEX(" & DataName & ",COUNTIF(" & DataName & ",""?*""))"
' ActiveWorkbook.Names(DV_Name).Comment = ""

'  Does Not Work
'  ActiveWorkbook.Names.Add Name:=DV_Name, RefersToR1C1:= _
'      "='Data Validation'!R3C" & ActiveCell.Column & ":INDEX(Range("K3:K14"),COUNTIF(Range("K3:K14"),""?*""))"
'  ActiveWorkbook.Names(DV_Name).Comment = ""


End Sub

 

[Hamartian_]

Fix was changing RefersToR1C1 to RefersTo

Code got cleaned up after that a bit, but that was the culprit that stumped mw