excel vba For循环中WorksheetFunction.Year的使用

2022-05-26 22:47| 来源: 网络整理| 查看: 265

在我的sheet DailyData A列中有一系列的日期。我需要找出它是否是闰年(在A列中)，并依赖于在F列中执行计算。(对于闰年，我需要使用366或365)

发现使用下面的公式，我可以检查它的闰年或不。

B=4要检查的a=Year

If a - (b * (a \ b)) = 0, then its leap year

现在，我使用下面的代码来执行计算，但它在a = WorksheetFunction.Year("A", J)行上给出了错误"Object doesn't support this property or method"

检查的正确方法是什么？

Sub DayTest() Dim MaxGain As Workbook Dim Main As Worksheet Dim DailyData As Worksheet Dim n As Long Dim J As Long Dim a As String, b As Integer Set MaxGain = Excel.Workbooks("MaxGain.xlsm") Set DailyData = MaxGain.Worksheets("DailyData") Set Main = MaxGain.Worksheets("Main") DailyData.Activate n = DailyData.Cells(Rows.Count, "A").End(xlUp).Row J = 1 b = 4 For J = 2 To n a = WorksheetFunction.Year("A", J) If a - (b * (a \ b)) = 0 Then DailyData.Range("F" & J).Value = Main.Range("B2").Value / 366 Else DailyData.Range("F" & J).Value = Main.Range("B2").Value / 365 End If Next End Sub

更新:我已经想好了。更改了行

a = WorksheetFunction.Year("A", J)

至

a = Year(DailyData.Range("A" & J))

【本文地址】

公司简介

联系我们