Solved

Can't add dynamic range programmatically

Posted on 2013-11-26
4
216 Views
Last Modified: 2013-11-26
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

0
Comment
Question by:MacroShadow
  • 2
  • 2
4 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
Comment Utility
Try

ActiveWorkbook.Names.Add Name:="Test", RefersTo:="=Master!R7C3:INDEX(Master!C3,SUMPRODUCT(--(Master!C3<>""""))+5)"
0
 
LVL 26

Author Closing Comment

by:MacroShadow
Comment Utility
Thank you, I can't believe I missed the double quotes!
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
You are welcome. I simply put your given formula in through the macro recorder and returned what was recorded.
0
 
LVL 26

Author Comment

by:MacroShadow
Comment Utility
Nice trick.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now