Named Range Create Via VBA

Hello

I am trying to create a Dynnamic named  range with VBA. But I am getting error.

ActiveWorkbook.Names.Add Name:="MyDynamicRange", _
                             RefersToR1C1:="=Extract201!$A$1:INDEX(Extract201!$O:$O,MATCH(REPT(""z"",20),Extract201!$A:$A))"


Please assist

Thank you
'
namedRngError.png
RayneAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
SteveConnect With a Mentor Commented:
Could it be that you have not used R1C1 references...
Have added a delete too, but not nescessary

'ActiveWorkbook.Names("MyDynamicRange").Delete
ActiveWorkbook.Names.Add Name:="MyDynamicRange", RefersToR1C1:= _
"=Extract201!R1C1:INDEX(Extract201!C15,MATCH(REPT(""z"",20),Extract201!C1))"

Open in new window

0
 
RayneAuthor Commented:
I can create it by pressing Ctrl + F3 in the named range manger. But I actually want to do it in VBA code as this should be dynamic...
0
 
RayneAuthor Commented:
thank you The_Barman :)
0
All Courses

From novice to tech pro — start learning today.