英文:
VBA COUNTIF with Custom Date Formats
问题
我需要帮助计算Excel范围内具有指定年份的单元格数量。我目前正在使用的代码如下：
yrRange = ThisWorkbook.Worksheets("Summary Tab").Range("A8:AE8")
yr = 2023
CountMonths = Application.Evaluate("SUM(IF(YEAR(yrRange)=yr,1,0))")
然而，即使日期如Jul-23（在“dd/mm/yyyy”格式中显示为01/07/2023）位于指定范围内，此代码返回0。
这里是我正在使用的自定义日期格式的截图。
英文:
I need help counting the number of cells with a specified year in an Excel range. The code I am currently using is as follows:
yrRange = ThisWorkbook.Worksheets("Summary Tab").Range("A8:AE8")
yr = 2023
CountMonths = Application.Evaluate("SUM(IF(YEAR(yrRange)=yr,1,0))")
However, this code returns 0 even if a date like Jul-23 (displayed as 01/07/2023 in "dd/mm/yyyy" format) is within the specified range.
Here is a screenshot of the custom date format I am using.
答案1
得分: 0
I failed to improve the code with "Application.Evaluate," but this code works for me:
Dim CountMonths As Long
Dim j As Long
Dim yr As String
yr = "2023"
CountMonths = 0
For j = 8 To Range("AE8").Column
If Year(CDate(Cells(8, j))) = yr Then
CountMonths = CountMonths + 1
End If
Next j
Debug.Print CountMonths
英文:
I failed to improve the code with "Application.Evaluate", but this code works for me:
Dim CountMonths As Long
Dim j As Long
Dim yr As String
yr = "2023"
CountMonths = 0
For j = 8 To Range("AE8").Column
If Year(CDate(Cells(8, j))) = yr Then
CountMonths = CountMonths + 1
End If
Next j
Debug.Print CountMonths
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论