current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I've been searching for hours for a function / vba macro solution to my problem - unfortunately to no avail... Maybe you guys can help?

I need to generate the 'gap' years within a range (year_start <-> year_end - separate inputs) i.e. for 1997-2002, the script should generate (preferable in one column, with values separated by ,) the following: 1997,1998,1999,2000,2001,2002.

Please help!

Your help is greatly appreciated in advance!

Thanks!

share|improve this question

1 Answer 1

up vote 0 down vote

if you must use VBA, instead of Excel's built in Autofill, try something like this:

dim iYear As Integer, iYearS as integer, iYearE as integer, iOffset as integer
dim oRangeStart as range

set orangestart=range("A1")

iYearS =1997
iYearE = 2002

for iYear = iYearS to iYearE
    orangestart.offset(iOffset,0).value=iyear
    iOffset=iOffset+1
next

but you could just Autofill surely?

share|improve this answer
    
Thank you - I'll try your script. Can't use Autofill as this has to happen on a 'case-by-case / row-by-row' basis automatically –  user2381937 Jul 12 '13 at 13:46
    
Can we update the script to generate the dates based on dynamic values? I.e. Content from Column A -> year_start and Column B-> year end. It should then generate the results in the corresponding cell of say, Column C –  user2381937 Jul 12 '13 at 13:58
    
How do I get the results into one cell? i.e. result entries split up with commas in one cell? -- Please help! –  user2381937 Jul 12 '13 at 14:10
    
@user2381937: inside the loop, you would remove the line iOffset=... to stop it from incrementing, and edit the line putting in the values to concatenate the values like orangestart.value = orangestart.value & ", " –  Philip Jul 15 '13 at 8:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.