Formula R1C1 Run-Time Error when trying to add a string variable to formula

[Luke71]

I've been trying to get a macro to add a formula to an excel sheet and I have it working if I hardcode the value or use a Long.
However as soon as I try to use a string variable I get "Run-Time error '1004 - Application-defined or object-defined error"

The code below does not throw an error:

Dim Search As String
Dim SearchCol As Long

Search = "Test"
SearchCol = 1

ActiveCell.Formula2R1C1 = "=UNIQUE(VSTACK(R[" & RowNumber & "]C:R[-1]C,UNIQUE(FILTER('Psoda Data'!R[-39]C[5]:R[4959]C[5],('Psoda Data'!R[-39]C[" & SearchCol & "]:R[4959]C[" & SearchCol & "]=" & SearchCol & ")*('Psoda Data'!R[-39]C[35]:R[4959]C[35]=""Y""),""No Results""))),,TRUE)"

And looks like this in the cell

=UNIQUE(VSTACK(F4:F78,UNIQUE(FILTER('Psoda Data'!K40:K5038,('Psoda Data'!D40:D5038=-2)*('Psoda Data'!AO40:AO5038="Y"),"No Results"))),,TRUE)

However when i change the third "SearchCol" variable to the string Variable "Search" I get the error:

Dim Search As String
Dim SearchCol As Long

Search = "Test"
SearchCol = 1

ActiveCell.Formula2R1C1 = "=UNIQUE(VSTACK(R[" & RowNumber & "]C:R[-1]C,UNIQUE(FILTER('Psoda Data'!R[-39]C[5]:R[4959]C[5],('Psoda Data'!R[-39]C[" & SearchCol & "]:R[4959]C[" & SearchCol & "]=" & Search & ")*('Psoda Data'!R[-39]C[35]:R[4959]C[35]=""Y""),""No Results""))),,TRUE)"

I can't find what I need to change but its something to do with the string, so I'm guessing I have some syntax wrong or I need to convert the string into something else.
Thanks in advance

 

[StephenCrump]

Welcome to the Forum!

Try:

ActiveCell.Formula2R1C1 = "=UNIQUE(VSTACK(R[" & RowNumber & "]C:R[-1]C,UNIQUE(FILTER('Psoda Data'!R[-39]C[5]:R[4959]C[5],('Psoda Data'!R[-39]C[" & SearchCol & "]:R[4959]C[" & SearchCol & "]=""" & Search & """)*('Psoda Data'!R[-39]C[35]:R[4959]C[35]=""Y""),""No Results""))),,TRUE)"

 

[Luke71]

Thankyou for this, worked perfectly. I tried single and double quotes but didn't realise triple quotes were a thing.

Are triple quotes standard R1C1 syntax for strings?

 

[StephenCrump]

I'm glad it worked. Thanks for posting back.

It's not a triple quote mark. It's a double.

We want Excel to make the comparison: ="Test". It's inside VBA quotes, so it needs to be a double quote ""Test""

The first "triple" quote is actually a double quote mark before a VBA closing quote mark, and the second is a double quote just after an opening VBA quote mark.

Another way this would work is:

ActiveCell.Formula2R1C1 = "=UNIQUE(VSTACK(R[" & RowNumber & "]C:R[-1]C,UNIQUE(FILTER('Psoda Data'!R[-39]C[5]:R[4959]C[5],('Psoda Data'!R[-39]C[" & SearchCol & "]:R[4959]C[" & SearchCol & "]=" & """" & Search & """" & ")*('Psoda Data'!R[-39]C[35]:R[4959]C[35]=""Y""),""No Results""))),,TRUE)"

Hope that makes sense?

 

[Luke71]

Thanks for the detailed explanation, that weirdly that does make sense.