• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

newly created Name Range not showing in name box

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
SBFurr
Asked:
SBFurr
  • 2
  • 2
1 Solution
 
jeffmeynCommented:
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
 
SBFurrAuthor Commented:
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
 
SBFurrAuthor Commented:
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
 
bruintjeCommented:
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
 
bruintjeCommented:
nope don't bother i'll take a drink and leave it for today
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now