SBFurr
asked on
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).Ra
"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
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).Ra
"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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
yeah!
thanks :)
I think my main problem is I've been staring at this too long.
Sasha
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
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
nope don't bother i'll take a drink and leave it for today
ASKER
=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