How to take values of specific cells instead of entire range

0
votes

I have a function that takes range of cells, but I need to modify it to take certain cells (not A7:A14, but A7, A9, A10, A11, etc). I don't know how many cells I will need, so it need to be able to take an unknown number of cells.

Here is the existing code: 

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
2
excelvba
Pressing ctrl while selecting the cells you want will do the trick. – Damian
No, it gives me an error, since the function expects just one argument which is range. – beginner17
Range("A1,A5,A6,A10") is just a range – Damian
I know what you're saying, but it just accepts A1:A5, or a single cell, when I put "," it won't work. :/ – beginner17
@beginner17 Can you please edit your issue and add a concrete example of your needs ? – Dorian

2 Answers

0
votes

ParamArray() is the way in VBA to add unspecified number of arguments. This is an example, concatenating anything together:

Public Function CountMe(ParamArray myArray() As Variant) As String

    Dim myVar As Variant
    Dim result As String

    For Each myVar In myArray
        If IsObject(myVar) Then
            Dim myCell As Range
            For Each myCell In myVar
                result = result & " " & myCell
            Next
        Else
            result = result & " " & myVar
        End If
    Next
    CountMe = result

End Function

This is how it is supposed to work:

enter image description here

0
votes

You could hand over the range as a string

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

the call the function with e.g.

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

Maybe not the most elegant way - but it works