时间戳

首页 > 解决方案 > 如何获取特定单元格的值而不是整个范围

excel - 如何获取特定单元格的值而不是整个范围

问题描述

我有一个接受单元格范围的函数，但我需要修改它以采用某些单元格（不是 A7:A14，而是 A7、A9、A10、A11 等）。我不知道我需要多少个细胞，所以它需要能够获取未知数量的细胞。

这是现有的代码：

Function CountC(rng As Range, Cell As Range)
Dim CellC As Range, ucoll As New Collection
For Each CellC In rng
    If CellC.Interior.Color = Cell.Interior.Color And worksheetFunction.IsText(CellC) Then
        On Error Resume Next
           If Len(CellC) > 0 Then ucoll.Add CellC, CStr(CellC)
        On Error GoTo 0
    End If
Next CellC
CountC = ucoll.Count
End Function

标签： excelvba

解决方案

您可以将范围作为字符串移交

Function CountC(rngString As String, Cell As Range)
Dim CellC As Range, ucoll As New Collection
Dim rng As Range
Set rng = Range(rngString)
For Each CellC In rng
    If CellC.Interior.Color = Cell.Interior.Color And WorksheetFunction.IsText(CellC) Then
        On Error Resume Next
        If Len(CellC) > 0 Then
            ucoll.Add CellC, CStr(CellC)
        End If
        On Error GoTo 0
    End If
Next CellC
CountC = ucoll.Count
End Function

用例如调用函数

=CountC("E1, E4, E8";D5)

也许不是最优雅的方式 - 但它有效

推荐阅读