Macro to insert list of dates with conditions

[Barking_Mad]

Hi,

I wonder if someone could help me with a macro?

I have one empty row (B3:K3) and one column (M4:M16) which the user populates with dates. There will be the same number of cells in the rows as there are cells in the columns. I also have a 'today's date' cell (M3) which displays, ermm today's date!

What i'd like to happen is everytime the worksheet is opened for the row (B3:K3) to be populated with the dates in (M4:M16). However it most only populate those empty cells with dates that are before and include today's date - and in the same order (NOT date order) that they appear in the column.

I've attached a small example spreadsheet of dates in the column that are entered and the blank fields in the row.

Dates.xlsx

[ARGK]

Hi
If I understand your question clearly you can do this without a macro. See solution attached.
Good luck.
Tony

[Barking_Mad]

Hi ARGK. Thanks for that.

Taking it just one step further can you help me work out how to get it to count the instances of the letter "n" that fall between the first date and the last date and all the students in the vertical column. So for instance in the attached example it should count 8 instances of "'n" in the table and not the other 6 instances the columns beyond the last date.

Dates.xlsx

[ARGK]

Hi
See example without using a macro you can use formulae. In range B2 to N2 I have put a formula in each of the cells to count the number of entries in each column that are equal to "n". Each subsequent column adds the previous column value so you have a running total. You just need to read the value above the last column that has a date in it.
Hope this helps.
Good luck.
Tony

[Barking_Mad]

Hi,

This equation does the trick - with the cells chosen as being the ones above the row where the final value is situated.

Please Login or Register  to view this content.