excel - 将大于逻辑应用于已自动过滤的行
问题描述
我已将以下过滤器应用于 Excel 工作表,该工作表根据开始日期和结束日期自动过滤列 O。
sht1.Range("$A$1:$X$3432").AutoFilter Field:=15, Criteria1:= _
">=" & CDbl(StartDate), Operator:=xlAnd, Criteria2:="<=" & CDbl(EndDate)
在这里,我根据开始日期和结束日期计算了可见的过滤行。
With sht1
Total_DCR = WorksheetFunction.Subtotal(102, ActiveSheet.Range("O1:O5000").Columns(1))
Debug.Print Total_DCR
End With
Dim i, delay_count As Integer
现在我想比较过滤列 O 和 X 中存在的日期是否大于逻辑。如果 O2 >X2 则将计数器增加 1。
For i = 2 To Total_DCR
If sht1.Range("O" & i).Value > sht1.Range("X" & i).Value Then
delay_count = delay_count + 1
Debug.Print delay_count
End If
Next
执行上述比较代码后,O 列中大于日期的计数显示错误数据。我觉得它也在考虑隐藏的行。在 O 列中,有 79 个日期介于开始日期和结束日期之间。当我在过滤的 O 和 X 之间选择大于逻辑以大于使用 IF(O2>X2,"YES","NO") 逻辑的逻辑时,大于行数为 53。我想使用 vba 代码实现相同的功能。但是我的行数超过了 76。我不知道这里出了什么问题。好心的帮助
解决方案
Private Sub CommandButton1_Click()
Dim F11, F22, Month, Year As Variant
Dim f_name1, f_name2
Dim wb1, wb2, wb3, wb4 As Workbook
Dim sht1, sht2, sht3, sht4 As Worksheet
Dim Total_DCR As Long
Dim StartDate, EndDate As Date
'Select Product Backlog File
F11 = Application.GetOpenFilename("check (*.xlsm*), *.xlsm*")
If (F11 <> vbNullString) Then
If (F11 <> "False") Then
f_name1 = F11
End If
End If
If (f_name1 = "") Then
MsgBox "The check file must be specified."
Exit Sub
End If
Set wb1 = Excel.Workbooks.Open(f_name1)
Set sht1 = wb1.Sheets("Product Backlog")
Set wb3 = ThisWorkbook
Set sht3 = wb3.Sheets("check")
With sht3
StartDate = sht3.Range("J3").Value
'Debug.Print StartDate
If IsDate(StartDate) = True Then
MsgBox ("The following string is a valid date expression")
Else
'if its not a date expression show a message box
MsgBox ("The following string is not a valid date expression")
End If
EndDate = sht3.Range("J4").Value
'Debug.Print EndDate
End With
wb1.Activate
sht1.Activate
sht1.Columns("O:O").Select
wb1.Worksheets("Product Backlog").Sort.SortFields.Clear
wb1.Worksheets("Product Backlog").Sort.SortFields.Add2 Key:=Range( _
"O1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With wb1.Worksheets("Product Backlog").Sort
.SetRange Range("O1:O3437")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
sht1.Range("$A$1:$X$3432").AutoFilter Field:=15, Criteria1:= _
">=" & CDbl(StartDate), Operator:=xlAnd, Criteria2:="<=" & CDbl(EndDate)
With sht1
Total_DCR = WorksheetFunction.Subtotal(102, ActiveSheet.Range("O1:O5000").Columns(1))
Debug.Print Total_DCR
End With
Dim rng As Range, rngVisible As Range, rngRow As Range, delay_count As Double
Set rng = Range("$A$1:$X$5000")
'// Get visible cells excluding header
With rng
Set rngVisible = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
End With
'// Must use "Cells" when dealing with "Rows" property
For Each rngRow In rngVisible.Rows
delay_count = delay_count + IIf(rngRow.Cells(1, "O") > rngRow.Cells(1, "X"), 1, 0)
Next
MsgBox ("total delay DCR is" & delay_count)
End Sub
@JohnyL 这是我的全部代码。检查工作簿包含从 2017 年到 2020 年的随机日期,每个月在 Col O 中。同样,Col X 也包含日期。代码必须针对 2020 年 2 月的日期过滤它们,然后在 O 和 X 日期之间进行比较。开始日期:01-02-2020 结束日期:20-02-2020
所有 AX 列都添加了过滤器下拉列表。应用自动过滤器时,它将仅过滤 2020 年 2 月的日期。AX 行在带有标题的第一行
@EylM 你介意回答这个吗