[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

newly created Name Range not showing in name box

Posted on 2002-03-28
5
Medium Priority
?
207 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 200 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

649 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