Excel模块-VBA:如果满足条件，则用于对有色单元格进行计数的功能 [英] Excel module - VBA: Function to count colored cells, if condition is met

问题描述

人！

我不经常在Excel中引用VBA，但是当我这样做时，我通过谷歌搜索找到了答案. 但是，无法满足我目前的需求.

I do not often refer to VBA in Excel, but when I do, I find answer by Googling. However, there is no answer for my current need.

我具有以下功能来计算范围内的颜色(可能的来源-

I have following function to count colours in range (possible source - http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm):

    Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)

    Dim rCell As Range
    Dim lCol As Long
    Dim vResult

    lCol = rColor.Interior.Color


    If SUM = True Then
        For Each rCell In rRange
            If rCell.Interior.Color = lCol Then
                vResult = WorksheetFunction.SUM(rCell)
            End If
        Next rCell
    Else
        For Each rCell In rRange
            If rCell.Interior.Color = lCol Then
                vResult = 1 + vResult
            End If
        Next rCell
    End If

    ColorFunction = vResult
End Function

我尝试将其扩展为在满足条件的情况下对特定范围内的颜色进行计数，但是失败了.

I have tried to extend it to count colours in specific range, if condition is met, but failed.

亲爱的同事们，我请您帮助我扩展功能以满足需要:

I ask you, dear colleagues, to help me to extend the function to fulfill the need:

计数特定范围内的颜色数，如果在另一个范围内遇到单词"foo" .

推荐答案

根据您的帖子，您要Count彩色单元格的数量，因此我对您的Function进行了以下修改，以便在您发布时起作用

According to your post, you want to Count the number of coloured cells, so I made the modifiactions below to your Function to work as you posted.

StrCond是第三个参数，它也是Optional，用于同时检查rCell.Value中的某个String.

StrCond is the third parameter, which is Optional to check also for a certain String in the rCell.Value.

Function ColorFunction(rColor As Range, rRange As Range, Optional StrCond As String) As Long

    Dim rCell As Range
    Dim lCol As Long
    Dim vResult As Long

    lCol = rColor.Interior.color

    For Each rCell In rRange
        If rCell.Interior.color = lCol Then
            If StrCond <> "" Then
                If rCell.Value = StrCond Then
                    vResult = vResult + 1
                End If
            Else
                vResult = vResult + 1
            End If
        End If
    Next rCell

    ColorFunction = vResult
End Function

测试此Function后，

屏幕快照的值:

单元格值的屏幕截图(按预期获取5):

这篇关于Excel模块-VBA:如果满足条件，则用于对有色单元格进行计数的功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！