vba - Excel 公式 -> VBA : Date of each Friday of the current month

Question

我有@ScottCraner 创建的当前公式，当粘贴到单元格 Q8 并拖到 Q12 时，将使用本月每个星期五的日期填充单元格:

代码:

=IFERROR(AGGREGATE(15,6,ROW(INDEX(A:A,EOMONTH(TODAY(),-1)+1):INDEX(A:A,EOMONTH(TODAY(),0)))/(WEEKDAY(ROW(INDEX(A:A,EOMONTH(TODAY(),-1)+1):INDEX(A:A,EOMONTH(TODAY(),0))),1)=6),ROW(1:1)),"-")

我试图将其转换为 VBA，因为我比公式更了解 VBA。但是想知道这里是否有人可以提供帮助。

真的很感激

Answer 1

这会迭代日期并将星期五放在 Q8:Q12

Sub myFri()
    Dim OArr(1 To 5, 1 To 1) As Variant
    Dim k As Long
    k = 1
    Dim i As Long
    For i = DateSerial(Year(Date), Month(Date), 1) To DateSerial(Year(Date), Month(Date) + 1, 0)
        If Weekday(i, vbSunday) = 7 Then
            OArr(k, 1) = i
            k = k + 1
        End If
    Next i

    If k = 5 Then OArr(k, 1) = "-"

    Worksheets("Sheet1").Range("Q8:Q12").Value = OArr
    Worksheets("Sheet1").Range("Q8:Q12").NumberFormat = "mm/dd/yyyy"
End Sub