CODEWITHSUNDEEP
excelvba
Edward Azar
Edward Azar

Reputation: 11

How to reference a collection in a formula

All I want to do is reference a collection in a formula. Like

Assume I already know how to make collections and arrays and have done so in my macro, Collection is literally a collection with only 1 column, and Textstring is an array vector.

'For every value of i in Textstring, I want to count the occurrence of that value in all the values of 'Collection'
For i = 1 to Whatever 
    =COUNTIF(Collection, """ & TextString(i) & """)
Next i

What I want to know is how to make aforementioned code work.

It should work like a normal countif: 

'ie: "=COUNTIF('Sheet1'!A1:A10, ""blah"")"

Upvotes: 1

Views: 106

Answers (1)

Skin
Skin

Reputation: 11262

You can't with COUNTIF, if you have a look at the arguments to the function, it expects a Range object.

enter image description here

The only suggestion I have is to do something like the below, i.e. write it out to a worksheet and then use that range as a parameter to your function ...

Public Sub CollectionToRange()
    Dim objCollection As New Collection, i As Long

    For i = 1 To 10
        objCollection.Add i
    Next

    ' Convert the collection to a range.
    For i = 1 To objCollection.Count
        Sheet1.Cells(i, 1) = objCollection.Item(i)
    Next

    ' Pass the range into the worksheet function.
    Debug.Print "CountIf Result = " & WorksheetFunction.CountIf(Sheet1.Range("A1:A" & objCollection.Count), ">3")

    ' Perform a clean up if required.
End Sub

enter image description here

Not sure if that helps or not.

Upvotes: 1

Related Questions