columns in excel to reflect array of values entered in previous two columns

**netvasi** · 09-16-2011, 05:33 AM

I need to write a VB Macro
requirement is like this
I have excel sheet
column1=2001
Column2=2010
I have to make Column3 as 2001,2002,2003,2004,2005,2006,2007,2008,2009,2010
Another example
PM Column1=1997
Column2=2000
Column should be 1997,1998,1999,2000
I mean Column3 should be 1997,1998,1999,2000

**TMS** · 09-16-2011, 06:02 AM

A UDF can do this. For example:

Please Login or Register  to view this content.

Copy and paste the code into a standard module and then use it as a normal function.

=CollateYears(A1,B1) gives 2001,2002,2003,2004,2005,2006,2007,2008,2009,2010 if A1 has 2001 and B1 has 2010.

Regards

**netvasi** · 09-19-2011, 01:12 AM

Thanks TMshucks..Yes it helped a lot but the first year is repeated twice ..For example..If A1 has 1995 B1 has 1997 C1 returns 1995,1995,1996,1997...means the first year is repeated..

Also I want to know what to do if the year is entered in two digits? I have 1998 in A1 and 2001 in B1 ..For this requirement what could be the UDF..Can you please clarify as i have an excell file with 17000+ records and years are entered in two digits..

With Regards
Netvasi

Originally Posted by TMShucks

A UDF can do this. For example:

Please Login or Register  to view this content.

Copy and paste the code into a standard module and then use it as a normal function.

=CollateYears(A1,B1) gives 2001,2002,2003,2004,2005,2006,2007,2008,2009,2010 if A1 has 2001 and B1 has 2010.

Regards