VBA-Countif范围与数组中的至少一个值匹配 [英] VBA - Countif Range matches at least one value from array

问题描述

我不知如何完成这项任务.如果该范围中单元格中的至少一个单词与数组匹配，则需要计算工作表中的范围.例如，如果单元格"B2"的句子中包含数组中的单词之一，则计为1，并且如果countif与数组匹配，则简单地计算范围.我的代码会更好地显示我的问题，因此，如果这一切令人困惑，我深表歉意.

I am at a loss as how to complete this task. I need to count a range in a sheet if at least one of the words in a cell in that range matches an array. For example, if cell "B2" has a sentence with one of the words that is in the array then count as one, and simply countif a range if it matches with an array. My code will better display my question, so I apologize if this is at all confusing.

With ThisWorkbook
    Dim Keywords As Variant
    Dim iVal As Double
    keyword = Array("*cold*", "*hot*", "*warm*", "*cool*", _
        "*temp*", "*thermostat*", "*heat*", "*temperature*", _
        "*not working*", "*see above*", "*broken*", "*freezing*", _
        "*warmer*", "*air conditioning*", "*humidity*", _
        "*humid*")
    iVal=Application.WorksheetFunction.CountIf(Range("B2",Range("B2").End(xlDown)),keyword)

    Dim rep As Worksheet
    Set rep = Worksheets("Report")
    rep.Range("A1") = iVal
End With

如我所示，如果数组中的这些单词之一在定义的范围Range("B2", Range("B2").End(xlDown))中的一个单元格中匹配，则在Worksheets("Report").Range("A1")中计数并显示值.感谢所有帮助，谢谢.

As I show, If one of those words in the array match in a cell in the range defined, Range("B2", Range("B2").End(xlDown)), then count and display value in the Worksheets("Report").Range("A1"). Any and all help is appreciated, thank you.

推荐答案

您需要比较两个列表以获取彼此之间的值-您的Range和Array.最简单的方法是通过嵌套循环.像这样:

You need to compare two lists for values within each other - your Range and the Array. The easiest way to do so is through nested loop. Like this:

Sub TestMe()

    With ThisWorkbook

        Dim Keywords    As Variant
        Dim iVal        As Long
        Dim myRange     As Range
        Dim myCell      As Range
        Dim bCount      As Boolean
        Dim myVal       As Variant

        keyword = Array("cold", "hot", "warm", "cool", _
                        "temp", "thermostat", "heat", "temperature", _
                        "not working", "see above", "broken", "freezing", _
                        "warmer", "air conditioning", "humidity", _
                        "humid")

        Set myRange = Columns(2).SpecialCells(2)

        For Each myCell In myRange
            bCount = False

            For Each myVal In keyword
                If InStr(1, myCell, myVal, vbTextCompare) Then bCount = True
            Next myVal

            If bCount Then iVal = iVal + 1                
        Next myCell

        Debug.Print iVal
    End With

End Sub

只要标志bCount设置为True，就完成计数.外循环的每次迭代都会将其重置为False. 只要我使用InStr()进行检查，我将删除*，那里的星星有点没用了.

The count is done whenever the flag bCount is set to True. It is reset to False by every iteration of the outer loop. I would remove the *, as far as I am using the InStr() to make the check and the stars are a bit useless there.

此外，Columns(2).SpecialCells(2)返回一个范围，该范围仅由值组成，这些值在列B中为非公式且非空.

Furthermore Columns(2).SpecialCells(2) returns a range, consisting only of values, which are non-formulas and non-empty in Column B.

这篇关于VBA-Countif范围与数组中的至少一个值匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！