VBA: Insert Formula Containing an Asterisk "*"

[zero269]

Hello,

I'm having some trouble with inserting a formula using VBA where the formula contains an asterisk (*).

My original working VBA code is this:

'Insert Filter Formula
ws.Range("A3").Formula2 = "=LET(x,FILTER(CHOOSECOLS(t_" & Name & _
    ",13,1,2,20,21,9,10,16,18),COUNTIF(l_OnHand,t_" & _
    Name & "[Book Status])),SORT(x,3))"

However, I needed to add a second criteria using * for AND. The following formula just adds *(ISBLANK(t_JohnDoe[Score])) which works with in cell testing:

=LET(x,FILTER(CHOOSECOLS(t_JohnDoe,13,1,2,20,21,9,10,16,18),(COUNTIF(l_OnHand,t_JohnDoe[Book Status]))*(ISBLANK(t_JohnDoe[Score]))),SORT(x,3))

Trying to use the following to insert this new formula keeps failing, and I suspect it's due to the * symbol.
Results in runtime error 1004 Application-defined or object-defined error

'Insert Filter Formula
ws.Range("A3").Formula2 = "=LET(x,FILTER(CHOOSECOLS(t_" & Name & ",13,1,2,20,21,9,10,16,18)," & _
    "COUNTIF(l_OnHand,t_" & Name & "[Book Status]))*(ISBLANK(t_" & Name & "[Score]))),SORT(x,3))"

I've tried separating the asterisk to no avail:

& "*" &

Any help would be greatly appreciated…

 

[StephenCrump]

Looks like you're missing the "(" in front of COUNTIF?

 

[zero269]

Looks like you're missing the "(" in front of COUNTIF?

Thanks Stephen, good catch!
Everything is working fine now...

ws.Range("A3").Formula2 = "=LET(x,FILTER(CHOOSECOLS(t_" & Name & ",13,1,2,20,21,9,10,16,18)," & _
        "(COUNTIF(l_OnHand,t_" & Name & "[Book Status]))*(ISBLANK(t_" & Name & "[Score]))),SORT(x,3))"