RefersTo command uses double quotes

[Brad E.]

Hi,

Two worksheets ("Entry" and "TABLES")
Entry!A1 = user-entered date
Entry!A2 = a Match formula to find which column to use on TABLES
(calculates to 2 for the date I have entered)
TABLES column A = my header column for all date entries
TABLES column B-F = entries which differ depending on date
TABLES row 1 = what I want my named range to refer to (TABLES!$C:$C, etc.)
TABLES row 2 = effective dates of when the columns should be used

Depending on the date that the user enters, I want to redefine my "MyDate"
Named Range to access different data.
I am using the WorksheetChange event so when the date field is modified, I
try to change "MyDate". I want "MyDate" to refer to a column on the TABLES
sheet (TABLES!$C:$C, for instance).

ActiveWorkbook.Names("MyDate").RefersTo =
Worksheets("TABLES").Range("$A$1").Offset(0,
Worksheets("Entry").Range("$A$2").Value).Value

When I go to the Name Manager after the previous command, MyDate shows in
the RefersTo column ="TABLES!$C:$C". However, using MyDate in an equation
doesn't work because MyDate is a text field. I need MyDate to refer to
=TABLES!$C:$C (no quotes). How can I accomplish this?

 

[OssieMac]

Hi Brad,

Am I interpreting your question correctly in that you want to name the
entire column and then you want to know how to address that column in further
code. If correct, try the following. I have used .Name at the end of the
range in lieu of
ActiveWorkbook.Names("MyDate").RefersTo = (Easier way to code).

Note that space and underscore at the end of a line is a line break in an
otherwise single line of code.

Worksheets("TABLES").Range("$A$1") _
.Offset(0, Worksheets("Entry") _
.Range("$A$2").Value) _
.EntireColumn.Name = "MyDate"

'I have just used select for the example.
'TABLES must be the active sheet before you can select a range on it.
Worksheets("TABLES").Select
Worksheets("TABLES").Range("MyDate").Select

 

[OssieMac]

Hi again Brad,

I have had another look at your question and now I am wondering if what you
really want is to be able to use the named range in a formula on the
worksheet not in VBA code as per my example. If this is correct, then you
just use the name in lieu of the column reference.

Following example of a VLOOKUP formula with column reference and replacing
column reference with the named range.

=VLOOKUP(A1,$C:$C,1,FALSE)

Replace $C:$C in the formula as follows.

=VLOOKUP(A1,MyDate,1,FALSE)

or if using xl2007 with scoping of named range and referencing from another
worksheet.

=VLOOKUP(A1,TABLES!MyDate,1,FALSE)