Can't add dynamic range programmatically

I'm trying to add a dynamic range to an Excel workbook. If I use the GUI it works fine, but when I try it programmatically it fails with runtime error 1004.

Name is: Test
Refers to is: =Master!$C$7:INDEX(Master!$C:$C,SUMPRODUCT(--(Master!$C:$C<>""))+5)

Here is the code:
ActiveWorkbook.Names.Add Name:="Test", RefersTo:="=Master!$C$7:INDEX(Master!$C:$C,SUMPRODUCT(--(Master!$C:$C<>""))+5)"

Open in new window

LVL 28
MacroShadowAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Try

ActiveWorkbook.Names.Add Name:="Test", RefersTo:="=Master!R7C3:INDEX(Master!C3,SUMPRODUCT(--(Master!C3<>""""))+5)"
0
 
MacroShadowAuthor Commented:
Thank you, I can't believe I missed the double quotes!
0
 
Saqib Husain, SyedEngineerCommented:
You are welcome. I simply put your given formula in through the macro recorder and returned what was recorded.
0
 
MacroShadowAuthor Commented:
Nice trick.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.