VBA-计数如果单元格范围显示的值等于期望值 [英] VBA - CountIf cell range displayed value is equal to desired value

问题描述

我在运行代码时遇到问题，因为 Range.Value 与 Range.NumberFormat 。例如，我的值是一个日期和时间，我想测试一周中的某天。我能够将数字格式设置为Sun-Sat，但是，我不确定如何使用 CountIf 进行测试。

I am having an issue running my code through, because the Range.Value is different than the Range.NumberFormat. For example, my value is a date and time and I would like to test for the day of the week. I was able to get the number format to be Sun-Sat, however, I am unsure how to test for it with CountIf.

Dim rep         as Worksheet
Dim day         As Range
Dim time        As Range
Dim wf          As WorksheetFunction
Set rep = Worksheets("Report")
Set day = rep.Range("H1", rep.Range("H1").End(xlDown))
Set time = rep.Range("I1", rep.Range("I1").End(xlDown))
Set wf = WorksheetFunction

With rep
    .Columns("H").NumberFormat = "dddd"
    .Columns("I").NumberFormat = "AM/PM"

    .Range("K1") = "Monday"
    .Range("K2") = "Tuesday"
    .Range("K3") = "Wednesday"
    .Range("K4") = "Thursday"
    .Range("K5") = "Friday"
    .Range("K6") = "Saturday"
    .Range("K7") = "Sunday"

    .Range("M1") = "AM"
    .Range("M2") = "PM"

        .Range("L1") = wf.CountIf(day, "Monday")
        .Range("L2") = wf.CountIf(day, "Tuesday")
        .Range("L3") = wf.CountIf(day, "Wednesday")
        .Range("L4") = wf.CountIf(day, "Thursday")
        .Range("L5") = wf.CountIf(day, "Friday")
        .Range("L6") = wf.CountIf(day, "Saturday")
        .Range("L7") = wf.CountIf(day, "Sunday")

    .Range("N1") = wf.CountIf(time, "AM")
    .Range("N2") = wf.CountIf(time, "PM")
End With

这是我到目前为止的内容，但对于 countif 的解决方案仅输出0。

This is what I have so far, but it only outputs 0 for the solution to the countif. Thanks in advance.

推荐答案

这是另一种计数方法。
注意，我在VBA阵列中完成了大多数工作，因为这比重复访问工作表要快得多：

Here's another way to do the counts. Note I did most of the "work" in VBA arrays as this is much faster than repeatedly accessing the worksheet:

编辑 ：要包括使用AM或PM时间计算H列中的条目数

EDIT: To include counting the number of entries in column H with AM or PM times

Option Explicit

Sub foo()
    Dim rep As Worksheet
    Dim rDts As Range
    Dim vDts As Variant
    Dim vCnts As Variant 'for the weekday count
    Dim vAP As Variant   'for the AM PM count
    Dim I As Long, J As Long

Set rep = Worksheets("sheet1")
'read dates into array -- faster processing
With rep
    vDts = .Range(.Cells(1, 8), .Cells(.Rows.Count, 8).End(xlUp))
End With

'Results array
ReDim vCnts(1 To 7, 1 To 2)
    vCnts(1, 1) = "Sunday"
    vCnts(2, 1) = "Monday"
    vCnts(3, 1) = "Tuesday"
    vCnts(4, 1) = "Wednesday"
    vCnts(5, 1) = "Thursday"
    vCnts(6, 1) = "Friday"
    vCnts(7, 1) = "Saturday"

ReDim vAP(1 To 2, 1 To 2)
    vAP(1, 1) = "AM"
    vAP(2, 1) = "PM"
'Do the counts
    For I = 1 To UBound(vDts, 1)
        J = Weekday(vDts(I, 1))
        vCnts(J, 2) = vCnts(J, 2) + 1

        'Check for AM or PM
        If Hour(vDts(I, 1)) < 12 Then
            vAP(1, 2) = vAP(1, 2) + 1
        Else
            vAP(2, 2) = vAP(2, 2) + 1
        End If

    Next I

'output the results
rep.Range("K1:L7").Value = vCnts
rep.Range("M1:N2").Value = vAP

End Sub

这篇关于VBA-计数如果单元格范围显示的值等于期望值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！