Solved: Using Variables in Named Ranges

[SamT]

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.



[vba]
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

[/vba]

[Bob Phillips]

I think you are missing activating the sheet, but wouldn't you just use Goto Sam?

[vba]

Application.Goto SheetName & NameString
[/vba]

[SamT]

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]

Doesn't what I suggested work with those values?

[SamT]

Yes.

Thanks. I thought I was going nuts.

Actually, I did for a while.

SamT

[SamT]

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

[vba]
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
[/vba]

[Bob Phillips]

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

[vba]

TestVar = Worksheets(SheetName).Range(RangeName) _
.Cells(Range(DeptCensus).Value + 2, 1).Value
[/vba]

[SamT]

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.

[vba]
TestVar = Worksheets(SheetName).Range(RangeName) _
.Cells(Range(DeptCensus).Value + 2, 1).Value
[/vba]

Thank you, thank you.
VBA kept hinting I needed the column number but...

Thank you Distinguised Lord


The results of your efforts will now be posted in Vic's "over my head" thread.

SamT