VBA Code to auto date cells

[data808]

I have a merged C2 and D2 cell where I will type in the month "January" for example and another cell G2 that I will type in the year 2022. I will do this for every month. Is there a way based on which month and year I type into these cells, to auto populate 1/1/22 - 1/31/22 from cells A4 - A34? I realize that not every month has 31 days and so if these happens for February for example and goes to 2/31/22, that is ok. I am just trying to create a template and we will only be using the dates that we need to and ignore the dates and don't exist. Thanks.

 

[My Aswer Is This]

With this type script you must press a button to get the script to run.
This is a modules script and does not run automatically when you enter a 1 in range A1

 

[data808]

If you want it to work as you type then you need to put it to be a Worksheet_Change event/sub not a regular Sub

Thank you very much @MARK858. Literally realized this and couple seconds later you replied. Now its working. Now that you have seen what I am working on though, would you be able to figure out what I really want? Thanks for the help!

 

[MARK858]

Thank you very much @MARK858. Literally realized this and couple seconds later you replied. Now its working. Now that you have seen what I am working on though, would you be able to figure out what I really want? Thanks for the help!

I'm afraid not as I don't work with merged cells

 

[My Aswer Is This]

If you want to use a Merged cell then just enter the value in one of the cells and use that cell as the reference in your script.

Now if you want the user to enter a Month name, I suggest using a Datavalidation list for the user to choose from. So, one user does not enter Jan and another user "January"

And if user enters January the all the days of that year you want entered in Clumn A

So what happens next when user enters "February"
Does this now start in row one again and overwrite all the previous dates in column A
Or start after last entry in column A

And do we really need to enter Year.

Nearly all years have 365 days except for leap years.
I'm not sure how to Take leap year into account

 

[My Aswer Is This]

I do now as I think you would need to enter the year.
So the date would be entered properly.

 

[data808]

If you want to use a Merged cell then just enter the value in one of the cells and use that cell as the reference in your script.

Now if you want the user to enter a Month name, I suggest using a Datavalidation list for the user to choose from. So, one user does not enter Jan and another user "January"

And if user enters January the all the days of that year you want entered in Clumn A

So what happens next when user enters "February"
Does this now start in row one again and overwrite all the previous dates in column A
Or start after last entry in column A

And do we really need to enter Year.

Nearly all years have 365 days except for leap years.
I'm not sure how to Take leap year into account

Well I wanted this to be a template so the user will enter the month and year into 2 different cells. The data validation idea is a good one and will use that so they always enter the correct format for the month. The year cell does seem odd but that's what I figured the code would need to be able to auto populate the correct year for cells A4:A34. I wanted those cells to auto populate in this format "mm/dd/yy" for all 31 days of the month. Again, I realize that not all months will have 31 days but we would just enter data for the days that we need. Unless there is a better solution for this problem? I can also post the actually file if you want to see what I'm talking about.

I would want this to auto populate cells A4-A34 for the whole month all the way down the list.

 

[My Aswer Is This]

Ok So what happens the second time the script runs?

Does it still start in range "A4" and over write the previously entered dates?
And you want this to run automatically when the month is entered or run when you press a button is pressed?

 

[data808]

Ok So what happens the second time the script runs?

Does it still start in range "A4" and over write the previously entered dates?
And you want this to run automatically when the month is entered or run when you press a button is pressed?

I will be making 12 separate files. One for each month. Every year we will do the same thing with a fresh batch of 12 files again for the user to enter the month and yaer. That's why the year is important for the auto populate feature.

Triggering this with a button would be fine.

 

[My Aswer Is This]

I will be making 12 separate files. One for each month. Every year we will do the same thing with a fresh batch of 12 files again for the user to enter the month and yaer. That's why the year is important for the auto populate feature.

Triggering this with a button would be fine.

So we will always start in "A4"

And Month will be in "C2"
And Year will be in "G2"

And you will be entering full Month name like January

I will get back with you in maybe 1 hour.
It's 2AM EST here in USA

 

[data808]

So we will always start in "A4"

And Month will be in "C2"
And Year will be in "G2"

And you will be entering full Month name like January

I will get back with you in maybe 1 hour.
It's 2AM EST here in USA

Thanks and no problem on getting back to me later. It's pretty late. Thanks for replying.

Yes we will always start in A4 as this will be a template. The best solution would be to use some kind of internal calendar so it knows to auto populate A4-A34 with the correct amount of days in that month. February only has 28 days most of the time so it would only populate cells A4-A30 for example. Not sure if that's possible but if it is, that would be ideal.