Auto fill macro开发需求：用For...Next循环实现多列数据自动填充

Here's a complete VBA macro that meets all your requirements, with clear explanations for the date increment logic you were confused about:

Sub AutoFillWeeklyData()
    Dim i As Long
    Dim startDate As Date
    
    ' Speed up execution by disabling screen updates
    Application.ScreenUpdating = False
    
    ' Set starting date to today's date
    startDate = Date
    
    ' Loop through rows 1 to 10000
    For i = 1 To 10000
        ' Column A: 1 to 10000 consecutive numbers
        Cells(i, 1).Value = i
        
        ' Column B: Incrementing date starting from today
        ' (i-1) ensures first row uses today, each next row adds 1 day
        Cells(i, 2).Value = startDate + (i - 1)
        
        ' Column C: Repeating 1-7 week sequence
        ' Mod 7 gives remainder 0-6, adding 1 shifts to 1-7
        Cells(i, 3).Value = (i - 1) Mod 7 + 1
        
        ' Column D: Full weekday names (Monday to Sunday)
        ' vbMonday sets Monday as day 1, WeekdayName converts number to text
        Cells(i, 4).Value = WeekdayName(Weekday(startDate + (i - 1), vbMonday), False)
    Next i
    
    ' Format Column B as a readable date (adjust format string as needed)
    Columns("B").NumberFormat = "yyyy-mm-dd"
    
    ' Re-enable screen updates
    Application.ScreenUpdating = True
    
    MsgBox "Data has been filled successfully!", vbInformation
End Sub

Key Breakdowns for Your Confusion Points: #

• Date Increment Logic: The line startDate + (i - 1) is the simple fix you need. startDate grabs today's date via the Date function. When i=1, we add 0 days (so it's today), i=2 adds 1 day (tomorrow), and so on—this ensures each row in Column B is the next consecutive date without gaps.

• Week Sequence (Column C): Using (i-1) Mod 7 +1 creates the repeating 1-7 pattern. Mod 7 calculates the remainder when i-1 is divided by 7, giving values 0-6. Adding 1 shifts this range to 1-7, which perfectly aligns with your week number requirement.

• Weekday Text (Column D):

  • Weekday(startDate + (i -1), vbMonday) returns a number 1-7 where 1 = Monday and 7 = Sunday (fixing Excel's default Sunday-first behavior).
  • WeekdayName(..., False) converts that number to the full weekday name (swap False with True if you want abbreviations like "Mon").

How to Use This Macro: #

1. Open your Excel workbook, press Alt + F11 to launch the VBA Editor.
2. Insert a new module via Insert > Module.
3. Paste the code into the module.
4. Run the macro by pressing F5 or assign it to a button directly in your worksheet for easier access.

内容的提问来源于stack exchange，提问作者Adam