ItalianPlatinum
Well-known Member
- Joined
- Mar 23, 2017
- Messages
- 843
- Office Version
- 365
- 2019
- Platform
- Windows
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?
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.
Formula is in column C (shown below). The variable I have as a string. It populates - should it not be as a string?
VBA Code:
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.