Solved

newly created Name Range not showing in name box

Posted on 2002-03-28
5
184 Views
Last Modified: 2008-03-17
I am creating a range using VBA to be used as the datasource for a chart.

the code for the range is:
Sub nameit2()
Dim wkstname As String
wkstname = ActiveSheet.Name

    ActiveWorkbook.Names.Add Name:="SqFtData", RefersToR1C1:= _
        "=OFFSET(" & wkstname & "!R3C11, COUNTA(" & wkstname & "!C11)-2,1)"
   
End Sub

If I look under the Menu Insert, Names, Define. I can see it listed but if I select the listing, nothing happens.
Also, when I click on the names box, it is not shown.

this is being used as a recordsource for a chart.  I took it off a website which gives this code to use for a chart when the data will change.

When I create the chart, I get an application error on this line:
   
    ActiveChart.SetSourceData Source:=Sheets(shtname).Range( _
        "SqFtData"), PlotBy:=xlColumns

shtname contains the active sheetname and holds the correct value.

ideas?

I hope this will be my last question on this project!

thanks,
Sasha
0
Comment
Question by:SBFurr
  • 2
  • 2
5 Comments
 
LVL 2

Accepted Solution

by:
jeffmeyn earned 50 total points
ID: 6902718
What does the reference look like if you click Insert > Name > Define?  I'm guessing it will show a text string, because that is how your proc is defining it.  Try defining a range object instead and setting that as the RefersTo.
0
 

Author Comment

by:SBFurr
ID: 6902761
When I open the Insert, Name, Define and select SqFtData it looks like this

=OFFSET(Beaver Valley!$K$3, COUNTA(Beaver Valley!$K:$K)-2,1)

I am not having much luck setting a range object.
I changed my nameit sub to look like:

Sub nameit2()
Dim wkstname As String
Dim refto As Range

wkstname = ActiveSheet.Name
Set refto = Set refto = Offset(wkstname!K3, CountA (wkstname!KK) - 2, 1)

ActiveWorkbook.Names.Add Name:="SqFtData", RefersToR1C1:=refto
   
End Sub

It dies when I try to set refTo, telling me that wkshtname!K3 is invalid - 'needs to be part of a collection.'

I am confused.

Sasha
0
 

Author Comment

by:SBFurr
ID: 6902828
ok a  little while of futzing with the code and I did  it!
yeah!
thanks :)

I think my main problem is I've been staring at this too long.

Sasha
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6902834
Hi Sasha,

Guess you forgot the R3 in the second R3C11 variable

   ActiveWorkbook.Names.Add Name:="SqFtData", RefersToR1C1:= _
       "=OFFSET(" & wkstname & "!R3C11, COUNTA(" & wkstname & "!R3C11)-2,1)"

:O)Bruintje
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6902841
nope don't bother i'll take a drink and leave it for today
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

708 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

12 Experts available now in Live!

Get 1:1 Help Now