PDA

View Full Version : Solved: Using Variables in Named Ranges



SamT
03-01-2010, 10:39 AM
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

SamT
03-01-2010, 11:42 AM
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?

SamT
03-01-2010, 12:27 PM
Yes.

Thanks. I thought I was going nuts.

Actually, I did for a while.

SamT

SamT
03-01-2010, 06:34 PM
: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

SamT
03-03-2010, 01:05 PM
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