Store Results of Filter Function in vba

[vba_pwning_me]

I have been increasingly frustrated so hopefully someone can help me. I'm trying to run the Filter(proj_name, assign_to = Target.Address) function in vba and store the results in a variable but I keep getting Error 2015 on my debug.

The idea is that I would like to store the array subset returned from the Filter function in a variable. Depending on what items are listed in the array their cells would have a different fill color. Here's what I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Dim SecCells As Variant
    
' The variable KeyCells contains the cells that will
    ' refer to column A on the Assignment Map tab .
    Set KeyCells = Range("assign_prsnl")
    
    SecCells = Application.Filter(proj_name, assign_to = Target.Address)

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
        
        For x = LBound(SecCells) To UBound(SecCells)
            Debug.Print SecCells
        Next x
End If
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
What are proj_name and assign_to?

 

[vba_pwning_me]

Hi & welcome to MrExcel.
What are proj_name and assign_to?

They're names I gave to columns D and E, respectively, on a tab named Resource Needs

 

[Fluff]

In that case try

    SecCells = Evaluate("Filter(proj_name, assign_to =" & Target.Address & ")")

 

[vba_pwning_me]

Brilliant!! That worked! Can you explain why I need 'evaluate'?

 

[Fluff]

VBA cannot handle array calculations of that sort, so you need to use Evaluate which will calculate the formula in xl.

 

[vba_pwning_me]

Interesting. Good to know. Thank you for your help, I really appreciate it!

 

[Fluff]

Glad to help & thanks for the feedback.