Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

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
0
Rayne
Asked:
Rayne
  • 2
1 Solution
 
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
 
SteveCommented:
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:
thank you The_Barman :)
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now