View Full Version : Solved: Using Variables in Named Ranges
I have a workbook with several identical sheets. Each sheet has several identical names, except that each names was defined as "SheetName!RangeName". The RefersTo property is like "="$A:A65536"."
I've looked at this for long enough that I'm repeating my mistakes.
:banghead: :bug: :motz2:
Option Explicit
Sub Test()
Dim SheetName As String
SheetName = "Sheet1"
Dim NameString As String
NameString = "!MyNamedRange"
Dim RangeName As Name
Set RangeName = ThisWorkbook.Names(SheetName & NameString) '.RefersToRange
Dim NamedRange As Range
' Set NamedRange = ThisWorkbook.Names(SheetName & NameString).RefersToRange
' Set NamedRange = Range(SheetName & NameString)
' I need to do either of:
NamedRange.Select ' or
Range(SheetName & NameString).Select
'I would prefer;
ThisWorkbook.NamedRange.Select
'Where ThisWorkbook is Assumed or Default
End Sub
Bob Phillips
03-01-2010, 10:59 AM
I think you are missing activating the sheet, but wouldn't you just use Goto Sam?
Application.Goto SheetName & NameString
I'm trying to use the value of SheetName!NamedRange
when both SheetName and NamedRange Values are set from other Variables.
strVar1 = "Sheet" & i
strVar2 = n & "Qtr"
NamedRange = strVar1 & "!" & strVar2
lngVar3 = NamedRange.Value
Sorry for the confusion, My brain feels like an earthworm in a mess of spagetti... totally lost.
SamT
Bob Phillips
03-01-2010, 12:18 PM
Doesn't what I suggested work with those values?
Yes.
Thanks. I thought I was going nuts.
Actually, I did for a while.
SamT
:banghead: :dunno :motz2:
There is a Worksheet("Shift1"), with a Named Range, "Shift1!wkd_PCU_DIR" RefersTo ="$B:B65536".
The Range is a column of numbers.
Sheet(Output) Contains:
A Formula,
=StaffRequired($A4,Day_of_Week,B$3)
where the value in A4 is "Shift_1", and the value of B3 is "PCU_DIR".
A Named Range, "Day_of_Week", Value is one of "Mon" to "Sun".
A Named Range, "PCU_Census", Value is 0 to 100
Here's the code I'm stuck on
Function StaffRequired(ByVal Shift As String, _
ByVal DayValue As String, _
ByVal Position As String) As Long
'RangeNamePrefix returns "wkd".
'DeptName returns "PCU".
If DayValue = "" Then Exit Function
Dim SheetName As String
SheetName = "Shift" & Right(Shift, 1)
Dim RangeName As String
RangeName = RangeNamePrefix(DayValue) & "_" & Position
Dim DeptCensus As String
DeptCensus = DeptName(Position) & "_Census"
'Works great So far.
Dim TestVar
'The next line kills Function without error!
'TestVar should be
'Range("Shift1!wkd_PCU_DIR).Cells(3).Value = 11
TestVar = Range(SheetName & "!" & RangeName) _
.Cells(Range(DeptCensus).Value + 2).Value
StaffRequired = TestVar
End Function
Bob Phillips
03-02-2010, 02:07 AM
The definition of the name wkd_PCU_Dir is wrong, it is a string "$B:$B65536" whereas you shoud define it as a range =$B:$B.
Also, you should use
TestVar = Worksheets(SheetName).Range(RangeName) _
.Cells(Range(DeptCensus).Value + 2, 1).Value
you shoud define it as a range =$B:$B.
Many thanks. That made me rewrite my Ng Namemaker to a VG Namemaker. And I hadn't even realized it was NG. :blush
TestVar = Worksheets(SheetName).Range(RangeName) _
.Cells(Range(DeptCensus).Value + 2, 1).Value
Thank you, thank you.
VBA kept hinting I needed the column number but...
Thank you Distinguised Lord
:bow: :bow: :bow: :bow: :bow: :bow: :bow:
The results of your efforts will now be posted in Vic's "over my head" thread.
SamT
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.