Help needed to writing a macro to put the year in column B of a tableusing Table nomenclature & the

  • Thread starter Thread starter Bob Benjamin
  • Start date Start date
B

Bob Benjamin

I have an Excel Table named "Dates" consisting of 2 columns: column A and column B.

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.
 
Try...

Sub Solution3()
Dim rng As Range
Set rng = Range("a2", Range("a" & Rows.Count).End(xlUp))
rng.Offset(, 1).Formula = "=year(a2)"
With rng.Offset(, 1)
.Value = .Value
End With
Set rng = Nothing
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Thanks Gary. Your macro certainly works flawlessly and is very fast, but I
am not certain it is the optimum solution in terms of performance.

On my computer:

- Solution3 took 1.70873559481424 seconds to process 100,000 dates
- Solution2 took 3.29125182459939 seconds to process 100,000 dates
- Solution1 took 1.53838717548297 seconds to process 100,000 dates

Since Solution1 is slightly faster than Solution3 I think that there is a
real performance benefit in using the Evaluate method to write formula
derived values to a range.

I wish I could figure out (or someone could show me) how to express
Solution1 in terms of structured table references. Since my dates are in a
Table and the columns A and B are named ranges, there has to be some way to
write a macro like Solution1 or Solution3 with structured table reference
terms such as Range("Dates[YY]").Value and "=Year(Dates[dates])" as in
Solution2.

Bob


"GS" wrote in message
Try...

Sub Solution3()
Dim rng As Range
Set rng = Range("a2", Range("a" & Rows.Count).End(xlUp))
rng.Offset(, 1).Formula = "=year(a2)"
With rng.Offset(, 1)
.Value = .Value
End With
Set rng = Nothing
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi Bob,

Am Fri, 26 Dec 2014 12:47:00 -0500 schrieb BobbyBenj:
- Solution3 took 1.70873559481424 seconds to process 100,000 dates
- Solution2 took 3.29125182459939 seconds to process 100,000 dates
- Solution1 took 1.53838717548297 seconds to process 100,000 dates
Click to expand...

try Solution4:

Sub Solution4()
Dim LRow As Long
Dim myRng As Range

Application.ScreenUpdating = False
LRow = Cells(Rows.Count, 1).End(xlUp).Row
Set myRng = Range("B2:B" & LRow)
With myRng
.Value = myRng.Offset(, -1).Value
.NumberFormat = "YY"
End With
Application.ScreenUpdating = True

End Sub


Regards
Claus Busch
 
Hi again,

Am Fri, 26 Dec 2014 18:58:00 +0100 schrieb Claus Busch:
try Solution4:
Click to expand...

or try Solution5:

Sub Solution5()
Dim LRow As Long, i As Long
Dim varTmp As Variant, varOut() As Variant

LRow = Cells(Rows.Count, 1).End(xlUp).Row
varTmp = Range("A2:A" & LRow)
ReDim Preserve varOut(UBound(varTmp) - 1, 0)
For i = LBound(varTmp) To UBound(varTmp)
varOut(i - 1, 0) = Year(varTmp(i, 1))
Next
Range("B2").Resize(UBound(varTmp)) = varOut

End Sub


Regards
Claus Busch
 
Hi Bob,

Am Fri, 26 Dec 2014 19:14:07 +0100 schrieb Claus Busch:
or try Solution5:
Click to expand...

if you want the year with 2 digits try:

Sub Solution5()
Dim LRow As Long, i As Long
Dim varTmp As Variant, varOut() As Variant

LRow = Cells(Rows.Count, 1).End(xlUp).Row
varTmp = Range("A2:A" & LRow)
ReDim Preserve varOut(UBound(varTmp) - 1, 0)
For i = LBound(varTmp) To UBound(varTmp)
varOut(i - 1, 0) = Year(varTmp(i, 1)) Mod 1000
Next
Range("B2").Resize(UBound(varTmp)) = varOut

End Sub


Regards
Claus B.
 
- Solution3 took 1.70873559481424 seconds to process 100,000 dates
- Solution2 took 3.29125182459939 seconds to process 100,000 dates
- Solution1 took 1.53838717548297 seconds to process 100,000 dates
Click to expand...

If you repeat your tests you'll find that the times will change. Given
the number of rows, .2 secs diff is insignificant in terms of
performance.
Since Solution1 is slightly faster than Solution3 I think that there
is a real performance benefit in using the Evaluate method to write
formula derived values to a range.
Click to expand...

Not entirely true, depending on the complexity of the formula! Fewer
worksheet processes will usually almost always be faster than VBA
processes.
I wish I could figure out (or someone could show me) how to express
Solution1 in terms of structured table references. Since my dates are
in a Table and the columns A and B are named ranges, there has to be
some way to write a macro like Solution1 or Solution3 with structured
table reference terms such as Range("Dates[YY]").Value and
"=Year(Dates[dates])" as in Solution2.
Click to expand...

I deliberately avoided doing so due to you deliberately creating a name
conflict that violates 'best practices'. Change your naming convention
so their is only unique names, then try running Solution2 with the new
refs.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Thanks Gary for all your help and excellent comments.

With of all of your and Claus' excellent feedback, I figured how to do what
I want (see solution 6) by modifying solution1 a little bit.

Following your good advice on best practices to make the table names unique.
I re-named:
- The table "DY"
- Column A - "D" - column A is the column with dates
- Column B - "Y" - column B is the column for the years

Sub solution6()

' Purpose: write the year of each date in column A in column B

Dim co As Integer
' column offset variable
co = Range("Dy[Y]").Column - Range("Dy[D]").Column ' calculate the
offset between date & year columns

With Range("dy[D]") ' changed from With Range("a2",
Range("a" & Rows.Count).End(xlUp))
.Offset(, co).Value = _
Evaluate("if(" & .Address & "<>"""",Year(" & .Address & "),"""")")
End With

End Sub

Have a Happy New Year and thanks again very much for all your help.
Bob

"GS" wrote in message
On my computer:

- Solution3 took 1.70873559481424 seconds to process 100,000 dates
- Solution2 took 3.29125182459939 seconds to process 100,000 dates
- Solution1 took 1.53838717548297 seconds to process 100,000 dates
Click to expand...

If you repeat your tests you'll find that the times will change. Given
the number of rows, .2 secs diff is insignificant in terms of
performance.
Since Solution1 is slightly faster than Solution3 I think that there is a
real performance benefit in using the Evaluate method to write formula
derived values to a range.
Click to expand...

Not entirely true, depending on the complexity of the formula! Fewer
worksheet processes will usually almost always be faster than VBA
processes.
I wish I could figure out (or someone could show me) how to express
Solution1 in terms of structured table references. Since my dates are in a
Table and the columns A and B are named ranges, there has to be some way
to write a macro like Solution1 or Solution3 with structured table
reference terms such as Range("Dates[YY]").Value and
"=Year(Dates[dates])" as in Solution2.
Click to expand...

I deliberately avoided doing so due to you deliberately creating a name
conflict that violates 'best practices'. Change your naming convention
so their is only unique names, then try running Solution2 with the new
refs.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top