根据日期范围对列进行计数 [英] Count columns based on date range

问题描述

我是VBA的新手。我正在尝试在VBA中完成以下操作，不确定如何执行此操作。

I'm new to VBA. I'm trying to accomplish the below thing in VBA and not sure how to do this.

1. 在工作表延迟的 K列中我的日期格式为 11/10/2016 16:17。 （该列中将有多个日期）


2. 在 O列中，单元格通过或失败中的价位很少

我想要做的是，在运行脚本时，它首先应该抛出一个对话框，要求用户输入日期范围。一旦用户输入了日期范围，它应该选择该列，然后在 o列中查找 Pass，并计算值并在特定单元格中对其进行更新。

What I want to do is, when the script is run, it should first throw up a dialogue box asking the user to enter the date range. Once the user gives the date range, it should select that columns and look out for "Pass" in "o" column and count the value and update it in a particular cell.

我尝试了以下代码，但我不确定如何使用日期范围条件添加计数值：

I have tried the below code, but I'm not sure how to add the count value using the date range criteria:

Sub WBR()
Dim s As String
Dim r As Range
Dim wf As WorksheetFunction
Dim xlSheet As Worksheet
Set xlSheet = ActiveWorkbook.Worksheets("Latency") 'sets the worksheet
Set wf = Application.WorksheetFunction
Set r = xlSheet.Range("O:O")                       'sets the range to search
s = "Pass"
[AE4] = wf.CountIf(r, s)
s = "Fail"
[AE5] = wf.CountIf(r, s)

With ActiveWorkbook.Worksheets("TT")
    [AE119] = wf.CountIfs(.Range("G:G"), "Yes", _
                          .Range("K:K"), "Tablet")
 End With

With ActiveWorkbook.Worksheets("TT")                'no of tickets processed
    [AE119] = wf.CountIfs(.Range("G:G"), "Yes", _
                          .Range("I:I"), "<>Duplicate TT", _
                          .Range("K:K"), "Tablet")
End With

End Sub

推荐答案

这与您现有的代码略有不同：

This is just a slight variation on your existing code:

Sub WBR()
    Dim wf As WorksheetFunction
    Set wf = Application.WorksheetFunction
    Dim MinDate As String
    Dim MaxDate As String
    MinDate = InputBox("Minimum Date")
    MaxDate = InputBox("Maximum Date")
    If Not (IsDate(MinDate) And IsDate(MaxDate)) Then
        MsgBox "You should have specified valid dates!"
        Exit Sub
    End If
    If CDate(MinDate) > CDate(MaxDate) Then
        MsgBox "You should have specified sensible dates!"
        Exit Sub
    End If

    With ActiveWorkbook.Worksheets("Latency")
        [AE4] = wf.CountIf(.Range("O:O"), "Pass")
        [AE5] = wf.CountIf(.Range("O:O"), "Fail")

        [AE6] = wf.CountIfs(.Range("K:K"), ">=" & CLng(CDate(MinDate)), _
                            .Range("K:K"), "<=" & Clng(CDate(MaxDate)), _
                            .Range("O:O"), "Pass")
    End With        
    With ActiveWorkbook.Worksheets("TT")
        [AE119] = wf.CountIfs(.Range("G:G"), "Yes", _
                              .Range("K:K"), "Tablet")
        'no of tickets processed
        [AE119] = wf.CountIfs(.Range("G:G"), "Yes", _
                              .Range("I:I"), "<>Duplicate TT", _
                              .Range("K:K"), "Tablet")
    End With
End Sub

这篇关于根据日期范围对列进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！