B

#### Bob Benjamin

Column A is a list of DATES. I want column B to be a list of the YEARS associated with the dates in column A. The year in cell B2 would be the result of the formula

=year(A2) etc.

The table name is "Dates"

Column A is also named "Dates" and

Column B is named "YY"

I would like an Excel VBA macro to do the following:

For each date in column A of the table "Dates", write the corresponding Year of that date in column B named "YY" but with the following constraints:

1. The macro should NOT use any loops.

2. I want the macro statements to be expressed in terms of Table referencessuch as "Dates[YY]" rather than Range references such as "Range "b2"

The following macro (Solution1) which I found at http://www.ozgrid.com/forum/showthread.php?t=172347 is very close to what I want.

Sub Solution1()

With Range("a2", Range("a" & Rows.Count).End(xlUp))

.Offset(, 1).Value = _

Evaluate("if(" & .Address & "<>"""",Year(" & .Address & "),"""")")

End With

End Sub

Solution1 is very fast but it does NOT use table references such as Dates[YY] so for me it is not ideal.

Another macro "Solution2" is also very close to what I want:

Sub Solution2()

Range("Dates[YY]").Value = "=Year(Dates[Dates])"

Range("Dates[YY]").Value = Range("Dates[YY]").Value

End Sub

I do not like solution2 because it is slower than solution1. It is slow because the first statement (Range("Dates[YY]").Value = "=Year(Dates[Dates])") only writes a formula such "=year(A2) in column A and then requires a second statement to convert the formulas in column B to year values suchas 2014, 2015 etc. It requires 2 steps rather than just 1.

There has to be a way to write a macro with the speed of solution1 which uses table references like in solution2 rather range references but I can notfigure out how to do that. Can you?

Any help or comments will be appreciated.