如何只考虑每个月的第一天? [英] How to only consider the first day of any month?

问题描述

如何确保以下代码仅考虑日期为01的日期?含义:仅是01.01.，01.02.，01.03等日期?

how can I make sure that this code below only considers dates where the day is the 01. ? Meaning: only dates like 01.01.,01.02.,01.03, etc ?

日期存储在wb.Source的第8列中.它们是真实日期.

The dates are stored in column 8 of my wb.Source. They are true dates.

Private Sub CommandButton2_Click() ' update averages




 Const YEAR = 2019

' open source workbook
Dim fname As String, wbSource As Workbook, wsSource As Worksheet
fname = Me.TextBox1.Text

If Len(fname) = 0 Then
   MsgBox "No file selected", vbCritical, "Error"
   Exit Sub
End If

Set wbSource = Workbooks.Open(fname, False, True) ' no link update, read only
Set wsSource = wbSource.Sheets("Sheet1") ' change to suit

Dim wb As Workbook, ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheets("Table 2") '

' scan down source workbook calc average





Dim iRow As Long, lastRow As Long
Dim sMth As String, iMth As Long
Dim count(12) As Long, sum(12) As Long


lastRow = wsSource.Cells(Rows.count, 1).End(xlUp).Row
For iRow = 1 To lastRow

  If IsDate(CDate(wsSource.Cells(iRow, 8).Value)) _
        And IsNumeric(wsSource.Cells(iRow, 30)) Then
  If Day(wsSource.Cells(iRow, 8)) = 1 Then
        iMth = Month(wsSource.Cells(iRow, 8))
        sum(iMth) = sum(iMth) + wsSource.Cells(iRow, 30)
        count(iMth) = count(iMth) + 1 '


      End If
End If

    Next

推荐答案

也许只使用 Day(date):

if Day( date ) = 1 then 'It is the first day of the month

您的代码应为:

Set wsSource = ThisWorkbook.Worksheets("Sheet1")

If IsDate(CDate(wsSource.Cells(iRow, 8).value)) _
            And IsNumeric(wsSource.Cells(iRow, 30)) Then
      if Day(wsSource.Cells(iRow, 8)) = 1 then 
            iMth = Month(wsSource.Cells(iRow, 8))   
            sum(iMth) = sum(iMth) + wsSource.Cells(iRow, 30) 
            count(iMth) = count(iMth) + 1 '
       end if
End If

这篇关于如何只考虑每个月的第一天?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！