How best to create an array of months between two dates?

Hi all,

I would like to create an array of months and years between two dates. I'd like to compare an 'effective date' to the current date and detemine what comes in between, for example:

Effective Date: 04/03/14 (today's date 17/01/14), would return: Jan 14, Feb 14, Mar 14
Effective Date: 18/12/13, would return: Dec 13, Jan 14
Effective Date: 02/01/14, returns just: Jan 14

I would like to use the output of this array to make a comparision to a named range which holds the current sheet names in the workbook. If the sheet exists then it'll run some code - if not then it'll create the sheet then run some code.
I'm fairly happy with how to create this part - but not how to extract the "MMM YY" from between two dates.

Any ideas?

Thanks, TC.

Hi there,

The following routines should do what you want:

Please Login or Register  to view this content.

Obviously you should alter the highlighted line to suit your own requirements.

Hope this helps - please let me know how you get on with it.

Regards,

Greg M

Please Login or Register  to view this content.

Hi Greg / Bsalv,

Thanks for coming back on this.

Greg, I tried your code and although it did not return any errors it dis not populate / return an array either. I think the porblem is here:

Please Login or Register  to view this content.

Perhaps it wasn't greater and that's why? I used a value of the 3/3/14 as the effective date. I tried to figure it out but I haven't had that much experience with arrays and i'm finding it a bit difficult to work out what's going on.

bsalv, your code worked perfectly - and I'm trying to adapt it to fit what I want to do with these names - check if a worksheet of this name exists and if not create it. I'm having a bit of a problem finding the right syntax to extract an 'item' from the array, e.g. "Feb 14". If it's not too much trouble, would you mind looking at the below and advising where I'm going wrong?

Please Login or Register  to view this content.

Much appreciated, TC

Please Login or Register  to view this content.

Hi Bsalv,

That didn't work for me - Type mismatch, which I remember from another problem and have found a solution: Cstr(n).

Here's the full code if it's of interest to anyone (compares two dates, builds an array of MMM YY - thanks to Bsalv's function - then used to create worksheets of the same name if they do not already exist:

Please Login or Register  to view this content.

Thanks again, TC.

the name of that new sheet was "jan 14" in my macro, in yours it 'll be "7", i suppose ??? that's why you use cstr(n)

Hi bsalv,

This code in my spreadsheet will also show "Jan 14". I won't pretend that I know all the in's and outs of how arrays work but i think the key was in the Cstr(n) and also in this line:

Please Login or Register  to view this content.

As opposed to:

Please Login or Register  to view this content.

I presume this affects how VBA handles the variables (being Dimmed as variant - VBA may chose that a string type is best in this situation).

Thanks, TC

n is a number, so you must be aware of what you do.
suppose n=7. Do you want the 7nd worksheet(= worksheets(7))or do you want the worksheet with the name "7"(=worksheets("7")).
So if it's numeric (and integer), excel thinks it's an indexnumber, if it's as string, then it's a name.
By using cstr(n), you translate the integer 7 into a string.
So excel doesn't have to choose and perhaps making a wrong choose.