FILTER formula criteria based of a variable with VBA

[ItalianPlatinum]

I have a FILTER formula where I need one of the criteria to come through as a text (CompList2). I don't have control over the source data so before it is asked I cannot change that to "general" as a workaround. Does anyone see an easy way to resolve? I provided parts of my VBA below that pertain to this condition.

Formula is in column C (shown below). The variable I have as a string. It populates - should it not be as a string?

Dim CompList, CompList2 As String
'Other code
WsFT.Names.Add Name:="CompList", RefersTo:=Range("B11")
CompList = Range("CompList")
'other code
'run for each 
  j = 0
  Do Until WsFT.Range("CompList").Offset(j, 0) = ""
    CompList2 = WsFT.Range("CompList").Offset(j, 0)
'other code including lr2 variable
With WsSum
    .Range("B2:B" & lr3).Formula2 = "=IFERROR(COUNTIFS(ALL!$C$2:$C$" & lr2 & "," & CompList2 & ",ALL!$D$2:$D$" & lr2 & ",A2),0)"
    .Range("C2:C" & lr3).Formula = "=IF(B2=0,0,COUNTA(UNIQUE(FILTER(ALL!$P$2:$P$" & lr2 & ",(ALL!$C$2:$C$" & lr2 & "=" & CompList2 & ")*(ALL!$D$2:$D$" & lr2 & "=A2))))-B2)"
    .Range("B1:E" & lr3).Value = .Range("B1:E" & lr3).Value
End With
'other code

How it looks after VBA

How it looks after I wrap it in "" and this is the desired value I should see

So how can I modify either the formula to work or how I conduct the usage of the variable. I do use the variable CompList2 in other places but I don't think it should impact those areas.

 

[dreid1011]

Give this a try:

"=IF(B2=0,0,COUNTA(UNIQUE(FILTER(ALL!$P$2:$P$" & lr2 & ",(ALL!$C$2:$C$" & lr2 & "=""" & CompList2 & """)*(ALL!$D$2:$D$" & lr2 & "=A2))))-B2)"

Added extra double quotes in to see if it will add them around CompList2 value.

 

[ItalianPlatinum]

And it was just that easy