Solved

newly created Name Range not showing in name box

Posted on 2002-03-28
5
190 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will show you how to use shortcut menus in the Access run-time environment.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

749 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