vba - Excel公式-> VBA:当月每个星期五的日期
问题描述
我有@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。但是想知道这里是否有人可以提供帮助。
真的很感激
解决方案
这会迭代日期并将星期五放在 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
推荐阅读
- r - .drop not working! Including Zero Frequency counts
- java - How do I get additions to a database made from one JavaFX tab to be reflected in a different JavaFX tab?
- python - How to specify timeout for firebase.put() in python?
- c# - 我应该如何像游戏“Lingo”一样为字母着色
- spring-boot - Not able to establish connection to the kerberos and SASL enabled kafka producer
- python - Comparing between entries in a list of lists using correct indexes
- nestjs - NESTJS 中的 TypeORM 实体 - 无法在模块外使用导入语句
- javascript - 我们可以在javascript中单击事件完成后执行操作吗?
- android - 从服务器转储数据库是不可能的吗?
- python - 如何制作打印和清除按钮 GUI,在 Python 中打印和清除标签?