?
Solved

newly created Name Range not showing in name box

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

800 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