Autofill macro to autofill dates up to 31 days regardless of the month

0 votes
The task is to format the provided data into more helpful lists. The fact that this task will be done frequently each month is difficult. Therefore, when a macro is constructed, the essential dates should adjust to all months. It is not necessary for them to be specific for adaptation. That is to say, regardless of the month, all months can finish on the 31st day because we may then leave those cells unfilled. For instance, February 2023 finishes on the 28th day, but since a month can only have a maximum of 31 days, we want it to be the final day, thus the autofill will end on February 31, 2023. The 29th, 30th, and 31st can then be empty.

Another point is that in order to retrieve the required dates, we need to specify the month and year before running the macro. I considered using "01.mm.yyyy" as a manual entry, and the macro will utilise that cell as a reference for autofill. To avoid having to wait for a manual entry, I also want to know if we may insert the current date by pressing Ctrl+Shift+; (semi-colon). The issue with this is that, for instance, the current date may begin on the third day and the autofill will continue as the third, fourth, fifth, etc.

In a cell, I've tried entering "01.02.2023." Then I created a macro where I dragged it down and had it fill in every day of that month automatically. But as I said, it came to an end on the 28th day. I then continued to autofill till the 28th day (because it is the lowest number of days a month can have, which can be filled with autofill no problem). As for the 29th, 30th, and 31st, I tried using something like "29.mm(of the input cell).yyyy(of the input cell), "30.mm(of the input cell).yyyy(of the input cell), and "31.mm(of the input cell).yyyy(of the input cell)". But I was unable to make it work.
Feb 5, 2023 in Others by Kithuzzz
• 38,010 points
236 views

1 answer to this question.

0 votes

If you wish to utilise a single formula, have 2016+ with dynamic arrays, and are specifying the Month and Year to use, then just say:

A1: =TEXT(ROW(INDEX($A:$A,1):INDEX($A:$A,31)),"00\.") & TEXT(Mnth,"00\.") & Yr

With Yr = 2023 and Mnth = 2

enter image description here

To produce this using VBA, try this:

Option Explicit
Sub MonthDays()
    Dim Yr As Long, y As String
    Dim Mnth As Long, m As String
    Dim vDates(1 To 31, 1 To 1) As Variant
    Dim I As Long
    Dim WS As Worksheet, rDest As Range
    
 Yr = Application.InputBox(Prompt:="Enter the Year as 'yyyy'", Title:="Year Input", Default:=Year(Date), Type:=1)
 Mnth = Application.InputBox(Prompt:="Enter the Month as 'm'", Title:="Month Input", Type:=1)
 
 y = Format(Yr, "0000")
 m = Format(Mnth, "00\.")
 
 'Create array with the date strings
 For I = 1 To 31
    vDates(I, 1) = Format(I, "00\.") & m & y
 Next I
 
 'write to the worksheet
 'below example shows one method
 Set WS = ThisWorkbook.Worksheets("Sheet1")

With WS
    Set rDest = Range(.Cells(1, 1), .Cells(31, 1))
End With

With rDest
    .ClearContents
    .NumberFormat = "@"
    .Value = vDates
    .EntireColumn.AutoFit
End With

End Sub
answered Feb 5, 2023 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

How can this code be modified to increase the number of months instead of days?

You have to use dateadd. For i = 1 ...READ MORE

answered Feb 3, 2023 in Others by narikkadan
• 63,700 points
320 views
0 votes
1 answer

Macro VBA code failing to execute a section of the code without an error

You get your code to make a ...READ MORE

answered Mar 24, 2023 in Others by Kithuzzz
• 38,010 points
390 views
0 votes
2 answers

How to get the URL of the current tab in Google Chrome?

Its so simple.... If you want to ...READ MORE

answered Aug 12, 2020 in Others by Steve
• 200 points
3,201 views
0 votes
1 answer

Where is the documentation to refer for coinbase api integration of Etherium coin currency in php?

Hey there! Please refer to the following ...READ MORE

answered Jan 25, 2019 in Others by Omkar
• 69,230 points
565 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
787 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,700 points
1,594 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,700 points
1,428 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,700 points
1,085 views
0 votes
1 answer

Excel: Is it possible to reorder the data in 2 columns to match up if they have a certain number of characters / a string in common?

Try this: =LET(files,A1:A4, URLs,B1:B4, f,BYROW(files,LAMBDA(r,TEX ...READ MORE

answered Jan 21, 2023 in Others by narikkadan
• 63,700 points
336 views
0 votes
1 answer

How to get the total of every monday to saturday or tuesday to sunday in a month dynamically

Try this: Option Explicit Sub demo() ...READ MORE

answered Jan 29, 2023 in Others by narikkadan
• 63,700 points
402 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP